Using the REPLACE function
-
I wanted to share a frustrating situation I had recently with the use of the REPLACE function. In a table in one of my databases, unwanted commas appeared in one column. I did a little research in Books Online and found the REPLACE function, which seemed to be able to do what I wanted to do. I then wrote the following t-sql statement:
UPDATE ItemInformation SET [Description] = REPLACE ([Description], ',', '')
I ran the statement and then reviewed the column again. The commas were still there.
I tried a number of different variations of the character to search for, but nothing worked. Sometimes I received an error message that the column didn't exist. Once I even replaced everything in the column with a blank space! (But like any good DBA, I had taken a backup first so I could recover.)
Finally, another DBA suggested that I run a query on the column to obtain it's current values. Then, I copied a comma from the results and pasted it into my REPLACE statement. Now it worked.
The problem with my first statement was that the comma in the database was not the same ASCII character as the comma from my keyboard. The data had come from a mainframe using a different character set. Therefore I was searching for a character that wasn't in my column, and nothing was replaced.
Hopefully, this will save someone else from making the same mistake in the future.
“If you're not outraged at the media, you haven't been paying attention.”
-
This was removed by the editor as SPAM
-
Note too that Unix will return a line feed and no carriage return!
Use something like this to find out what the ascii value of each character is:
declare @i as integer
declare @string as varchar(25)
set @string = 'test!,this_stri"ng'
set @i = 1
while @i <= len(@string)
begin
print substring(@string,@i,1)
print ascii(substring(@string,@i,1))
set @i = @i + 1
end
[font="Courier New"]ZenDada[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply