Is there a better way to Update fields? 1

[eyhandle]

Is there a better way to Update fields?Below works, but is there a better way?


1.Delete all hyphens, apostrophes, blankd, periods, II,

Update tablename
set first_name= replace(first_name, 'II', "&quot

Update tablename
set first_name= replace(first_name, '-', "&quot


Update tablename
set first_name= replace(first_name, ''', "&quot


Thanks so much

 

[tlbroadbent]

You can nest the executions of the REPLACE function. Then you only need to make one pass of the table. I recommend adding a Where clause so the query only updates rows that contain the characters to be changed.

Update tablename Set first_name =
replace(replace(replace(first_name,' II',''),'-',''),char(39),'')
Where charindex(' II',first_name)>0
Or charindex('-',first_name)>0
Or charindex(char(39),first_name)>0

Note: char(39) is a single quote '

You need to be carefule about changing II to empty space. A name might contain that string. I added a space before the 'II' in the code so the query checks for ' II'. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm