December 7, 2004 at 3:18 pm
I have a table in which certain columns contain a character which I wish to get rid of. What's the best technique to use to get rid of this character?
The character can appear anywhere within the string value.
December 7, 2004 at 3:40 pm
If the occurrance of the character is always to be removed then do and update with
set col = replace(col, 'character','')
and so you minimize the number of updates use
where charindex('character',col) > 0
Tes it to make sure you get the desired effect before using on production thou.
December 8, 2004 at 6:39 am
I came up with the following based on your example but I get an "Incorrect syntax near the keyword 'where'." message on line 4. Can you help me out with what I am doing wrong.
declare @string char(32)
set @string = 'abxcxefg'
set @string = replace(@string,'x','')
where charindex('x',@string) > 0
select @string
Thanks,
Howard
December 8, 2004 at 7:47 am
what Antares meant to say is :
Update MyTable set MyColumn = replace (MyColumn, 'x', '')
where charindex ('x', MyColumn) > 0
that way only the columns that actually need modifying would be affected... reducing disk I/O and update speed.
December 8, 2004 at 7:50 am
a 'where' clause can not be used with a 'SET' statement, in your example, change the 'SET' to a 'SELECT' as follows:
declare @string char(32)
set @string = 'abxcxefg'
select @string = replace(@string,'x','')
where charindex('x',@string) > 0
select @string
Of course, when you apply this logic to a table 'UPDATE' statement, the where clause will also be valid.
December 8, 2004 at 8:03 am
In your case as you are using a variable and not a col in a table you would not use the WHERE clause. The previous poster stated it was a column in a table so they would need it to narrow rows to be affected.
Ex.
UPDATE
tblX
SET
Col1 = replace(Col1,'x','')
WHERE
charindex('x',Col1) > 0
The where clause controls the rows to be updated so it does not affect all. If for example only 300 rows out 12 million need to be updated the where will enforce to only do those 300 otherwise 12 million rows will be affected which means more transactions and more logging to the log file take place.
December 8, 2004 at 8:22 am
Thanks to all for the excellent responses.
Howard
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply