I have a table called exchange and field called address. The rows(1400+) in the field look like:
MS:VA/Celcmv/VHACLEADAM%SMTP:Doe.Jane@med.va.gov%X200:c=US;a= ;p=av;o=Celcmv;s=Doe;g=Jane;
How do I remove everything to the left of doe.jane@med.va.gov and everything to the right of doe.jane@med.va.gov using query analyzer? Thank you in advance...
Can someone please suggest a function to remove the last 3 characters from a column? I was thinking of the LEN function, but I am unsure of the syntax.
Thanks!
Lisa
I have a table and one of the column have junk characters in it, how can I remove the junk characters?.
Eg : Employee
Eid Ename
1 a�
2 �ddd
how can i remove Junk characters and get only Enames from above table.
Hello all,
I have a table named users. It consists of user names, user ids, etc... The problem is that whoever designed the ASP code before me allowed people to enter info in any format they want. This poses a problem because now the name can have 1, 2, or sometimes 3 spaces in between the last and first names. And sometimes the middle initial is used with a period following it. This creates problems when I am trying to execute a Select statement since it won't match if there are an unknown number of spaces in the string and throws an error when a period is used.
Is there a SQL query I can execute to change all the user_names into a format such as the following:
LastName, FirstName MiddleInitial
Like I said, it is already almost the same, just has too many spaces and some have periods after the middle initial
I've got one SQL Server 7.0 table with a "Decsciption" Column of length 4000. The values in this column contains "End of Line" ASCII Character. The ASCII Value of this character is 10. I'm not able to remove this ASCII Character. I tried by using REPLACE function. But i could not remove that character.
Any thoughts are welcome,
Regards,
Santha.
Folks ... I have a pipe-delimited ASCII text file with a lot ofdifferent non-printing characters. Rather than try and figure out allthe non-printing characters that exist in this 17+ million recorddatabase, I was hoping someone might have already written a scriptthey'd be willing to share that would remove all non-printingcharacters from an ASCII file?Thanks,Ralph NobleJoin Bytes!
View 1 Replies View Related
here is my data
112344*100
23*34444
I want to remove * and make sure the length is 11 digits and and add leading 0's
Desired data
00112344100
00002334444
How will i be able to achieve this..
Hello,
I am using the following expression to strip the last 11 characters in a field group and it returns an error saying that Len cannot use a negative number. It must be 0 or greater. Is there a better alternative?
Code Snippet
left(Fields!TestName.Value,len(Fields!TestName.Value)-11))
Hi,
I was wondering what would be the best way to remove special characters like, '-', '&' '(',')','#','*', etc... from a number string. To be specific a phone Number string where the string is >= 10.
Thanks, Mark
I have a table with several columns of information that I wish to set up some form of schedule to go through this data and remove any special characters that may interfere with other code processes.
Mainly the coma's and the apostrophes. It really messes with my asp pages and scripts when retrieving this information and trying to do other things with it, so I need to figure out how to remove these from the tables so it does not cause these issues.
Knowing this, I cannot figure out how to keep the data in the row/column and just extract the special characters from that data. The other problem is, everything I try requires me to insert either a coma or apostrophe as part of the code string which in lies my issue.
How can I parse through my data, leave the data as-is, but just get rid of coma's, apostrophes, and double quotes?
Does anyone have a basic example that I can use to expand on?
Hi I am trying to strip out any non-alpha characters from a field.
i.e. Field = ABC"_IT8*$ should return: ABCIT8
I am writing a loop to do this for all values of a field. The script runs, but hangs....please could somebody advise on the code below...:
I run the script but it doesn't seem to finish. Can anybody see any issues with the code:
DECLARE @Index SMALLINT,
@MATCH_Supplier_name varchar(500),
@Counter numeric,
@Max numeric
-- @sqlstring varchar(500)
SET @Counter = 1
SET @Max = (SELECT Max(DTect_Supplier_SRN) FROM SUPPLIER_TABLE_TEST)
WHILE @Counter <@Max
BEGIN
SET @MATCH_Supplier_name = (SELECT Match_Supplier_Name FROM SUPPLIER_TABLE_TEST WHERE @Counter = DTect_Supplier_SRN)
SET @Index = LEN(@MATCH_Supplier_name)
WHILE @Index > = 1
SET @MATCH_Supplier_name = CASE
WHEN SUBSTRING(@MATCH_Supplier_name, @Index, 1) LIKE '[a-zA-Z]' TH EN SUBSTRING(@MATCH_Supplier_name, @Index, 1)
WHEN SUBSTRING(@MATCH_Supplier_name, @Index, 1) LIKE '[0-9]' THEN SUBSTRING(@MATCH_Supplier_name, @Index, 1)
ELSE ''
END + @MATCH_Supplier_name
SET @Index = @Index - 1
--PRINT @MATCH_Supplier_name
SET @Counter = @Counter + 1
END
I usually do this through Access so I'm not too familiar with the string functions in SQL. My question is, how do you remove characters from the middle of a string?
Ex:
String value is 10 characters long.
The string value is X000001250.
The end result should look like, X1250.
I've tried mixing/matching multiple string functions with no success. The only solution I have come up with removes ALL of the zeros, including the tailing zero. The goal is to only remove the consecutive zeroes in the middle of the string.
I have a varchar field which contains some Greek characters (α, β, γ, etc...) among the regular Latin characters. I need to replace these characters with a word (alpha, beta, gamma etc...). When I try to do this, I find that it is also replacing some of the Latin characters.
DECLARE @Letters TABLE (Letter NVARCHAR(10))
INSERT INTO @Letters VALUES ('a'), ('A'), ('b'), ('B'), ('α')
SELECTLetter, REPLACE(Letter,'α','alpha')
FROM@Letters
In this case, the "α" is being replaced, but so are "a" and "A".
I have tried changing the datatype from varchar to nvarchar and also changing the collation.
I’m getting ASCII characters in one column of my table. So I want to replace same column value in NON ASCII characters.
Note – values in column must be same
Hi,
I have one column in which i have Alpha-numeric data like
COLUMN X
-----------------------
+91 (876) 098 6789
1-567-987-7655
.
.
.
.
so on.
I want to remove Non-numeric characters from above (space,'(',')',+,........)
i want to write something generic (suppose some function to which i pass the column)
thanks in advance,
Mandip
How to remove identity property from a column throught SQL statement?
View 4 Replies View RelatedHi all,
I have one table in which one column contains duplicate values. My question is how i can use T-SQL so that i can retrive values for all columns in the table which are distinct and retriving the single value from column which contains duplicate values.
(I know distinct)
Thanks in advance.
Minesh.
Hi all
i want to remove text from my column name using query.
for example
i have the product name like "silver 8' trampoline pack "
i need to remove "silver 8' " and want to display only trampoline pack
similarly if I have product name like "gold 8' trampoline pack"
i need to display only trampoline pack.
can anybody help me in this regard?
thanks
We can easily remove identity columnn through enterprise manager but how can it be done through transact sql?
The only way i found is to create a new column and pass values if identity column in it and then remove this identity column is there any better method of doing it?
Hi,
I have the following tables :
Code Block
Create table #EmployeeList(empname nvarchar(20), emptype char(5))
Insert INTO #EmployeeList('Cary zzz',null);
Insert INTO #EmployeeList('01 Jack',null);
Insert INTO #EmployeeList('02 Tommy',null);
Insert INTO #EmployeeList('03 Ricardo',null);
Insert INTO #EmployeeList('04 Jack',null);
Insert INTO #EmployeeList('Les zzz',null);
Insert INTO #EmployeeList('05 Tim',null);
The final data looks like this :
Cary zzz NULL
01 Jack NULL
02 Tommy NULL
03 Ricardo NULL
04 Jack NULL
Les zzz NULL
05 Tim NULL
1. I want to delete all rows which have 'zzz' in it.
2. I want to remove the numbers from the empname column
Code Block
Expected Output :
Jack NULL
Tommy NULL
Ricardo NULL
Jack NULL
Tim NULL
Can anyone help me please with the query?
thanks.
Using the following:
select hl, substring(hl,1,patindex('%/%',hl)) as test
from appointment
returns
hl test
A PCM/RODRIGUEZ A PCM/
Y OPTOMETRY/VISUAL FIELD TESTSY OPTOMETRY/
W DENTAL/DUNDON W DENTAL/
Y LAB
Y PCM/NEMES F/U Y PCM/
W NUTRITION/FIRM-A (ROOM E116)W NUTRITION/
W FIRM-A/SILVER/ABBOUD IIW FIRM-A/
I want to be able to remove the first 2 digits and the / to just have the clinic only remaining. Note that Y LAB is not listed in the test column..why? Any help is greatly apprecitated. Thank you....
how do i update a table which has like two strings in 1 column like
blog, joe ?
i want to strip the joe into a new field and the blog into another field
update Agency
set firstname= substring(firstname,charindex(' ',firstname)+1 ,len(firstname))
i managed to strip the first name which is the string at the back but not the last name which is the string at the front
I am trying to get people from my table that have closed accounts. However, in my table many people have more than one account. They will have multiple closed accounts and some active accounts. I need to get the people with only closed accounts.
Values in the table
Code:
name surname status Closed Number
----------- --------- ----------- ------------- ----------------------------
Jeff Burns closed 2012/01/01 142
Tina Drewmor closed 2008/05/20 546
Jeff Burns active 1900/01/01 354
Kyle Higgin active 1900/01/01 851
Tina Drewmor closed 2009/04/14 154
The query I am using so far is:
Code:
select
d.name,
d.surname,
s.status,
s.closed,
s.number
from
d d inner join s s on d.number = s.number
where
s.status = 'closed'
What I need to see in the results
Code:
name surname status Closed Number
----------- --------- ----------- ------------ -----------------------------
Tina Drewmor closed 2008/05/20 546
Tina Drewmor closed 2009/04/14 154
I have a simple statement where I am trying to remove all commas that may be in the xn_workordeld column.
SELECT
xn_workordeld = case replace(xn_workordeld, ',', '') as "WORKORDER LD"
from workorder
Is this possible to do using regular expressions in sql 2005?
View 1 Replies View RelatedHow to remove an Identity property for an Identity Column
View 1 Replies View RelatedHello,This is a simple question, hopefully with a simple answer. I havean nvarchar column of length 255. In one of the rows I have thefollowing sentance - 'See the brown ball bounce'. Is it possible touse a command to remove all of the spaces in that sentance, so thatthe sentance reads 'Seethebrownballbounce'? As you can see, I am notjust interested in getting rid of the trailing and leading spaces.Thanks,Billy
View 1 Replies View RelatedI need an SQL statement that will hide a field in a column without removing it from the table.
View 3 Replies View RelatedHello,
I recently used the REPLACE command, as described in a previous topic on this forum, to remove unwanted commas however I've now got a new problem, the column has become half a mile long. I was asked to raise a new topic and give examples, see below:
CAN ANYONE TELL ME:
1. Why is the column now bigger?
2. How can I redue the size of the column to it's origional size?
I have already attempted to use CONVERT, RTRIM and CAST around the replace command, all give an error.
Example query and result before REPLACE:
select ICMAFinInstName,CptyCode from tradedetails
ICMAFinInstName CptyCode
---------------------------------------------------------------------- ----------------
Example query and result using REPLACE:
select replace (ICMAFinInstName,',',' ')AS NoCommaInst,CptyCode from tradedetails
NoCommaInstrument CptyCode
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------
(I see in the preview that this forum has removed the spaces between the titles, but the dashes (--) show the size of the columns
Thanks
JM
I have a table . It has a nullable column called AccountNumber, whichis of varchar type. The AccountNumber is alpha-numeric. I want to takedata from this table and process it for my application. Before doingthat I would like to filter out duplicate AccountNumbers. I get most ofthe duplicates filtered out by using this query:select * from customerswhere AccountNumber NOT IN (select AccountNumber from customers whereAccountNumber <> '' group by AccountNumber having count(AccountNumber)[color=blue]> 1)[/color]But there are few duplicate entries where the actual AccountNumber issame, but there is a trailing space in first one, and hence thisduplicate records are not getting filtered out. e.g"abc123<white-space>" and "abc123" are considered two different entriesby above query.I ran a query like :update customers set AccountNumber = LTRIM(RTRIM(AccountNumber)But even after this query, the trailing space remains, and I am notable to filter out those entries.Am I missing anything here? Can somebody help me in making sure Ifilter out all duplicate entries ?Thanks,Rad
View 3 Replies View Related
OutCollection[1] holds the erroroutput columncollection.
metaData.OutputCollection[1].OutputColumnCollection.RemoveObjectByID(errOutputCol.ID);
some times 1 Or 2 columns gets deleted, after that the exception is raised.
I'm executing the following...
select COL1, min(COL2) from TABLE group by COL1
the table has many duplicate entries, where COL2 is the primary key and unique, but its the duplicate COL1 entries that have to be removed.
I was hoping a simple
"delete from table where COL1 not in (select COL1, min(COL2) from TABLE group by COL1)"
would do the trick, but obviously in returning two columns from the subselect this won't work. Can I hide the COL2 output from the query that will be put in the subselect?
this is a one-off thing, so i'm not overly concerned about overhead or elegance. just need to make it so.
tia
a
I have a stored procedure that returns a single row based on a parameter of employee ID. This particular procedure uses a CTE to traverse our org structure. One of the columns is returning a delimited string of Windows login values and due to the business rules, it can contain duplicate values. I need to have that column contain only unique values - no dupes.
For example, this one column could contain something like this:
domainuser1;domainuser2;domainuser2;domainuser 3;
The need is to convert to this:
domainuser1;domainuser2;domainuser3;
I know that's a tall order.