May 5, 2005 at 5:05 am
I am using SQL server 2000 and have a table , in which one of the fields has multiple commas that need to be removed.
An eg. of the field is CR 12,455
I tried the following:
Update TABLE set field = replace(field,'[,]','')
and the result said that it had updated all the rows , but there had been no change. What am I doing wrong.
Thanks in advance.
May 5, 2005 at 5:10 am
Remove the square brackets from around the comma REPLACE(field, ',', '')
Good Hunting!
AJ Ahrens
webmaster@kritter.net
May 5, 2005 at 6:00 am
Thanks . That worked. Now, I have another question for the same field.In some cases, the same field has a value like this :
CR 12,345 , CR 34,456
and I should not be replacing the delimiter commas, only the commas that occur between the number have to be removed? I was actually planning to remove all the commas and then search for the the second occurrence of CR and put a comma before that. Is there another way of doing it ?
May 5, 2005 at 8:26 am
Maybe you could create an UDF that returns the string without the commas in the number values - something like this....
DECLARE @position int, @string varchar(35), @newstring varchar(35)
-- Initialize the variables.
SET @position = 1
SET @string = 'CR 12,345 , CR 34,456'
WHILE @position 32
BEGIN
set @string = substring(@string, 1, (@position - 1)) + substring(@string, (@position + 1),(datalength(@string) - (@position- 1)))
PRINT @string
END
SET @position = @position + 1
END
**ASCII stupid question, get a stupid ANSI !!!**
May 5, 2005 at 8:55 am
Thanku, That worked great.
May 5, 2005 at 9:26 am
you're welcome - thx. for feedback!
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply