Using REPLACE in an UPDATE statement
By Bill Graziano
on 20 January 2002
| 8 Comments
| Tags: UPDATE
Andre writes "Howdy, I am converting an asp application into asp.net. As I have a couple of tables including all the filenames of the app. Now I need to convert the .asp extension with the .aspx extension. How do I do this? It's probably not as simple as: Update sid_tbl.sidnamn Set '.asp' = '.aspx'; ..or is it?" (This article has been updated through SQL Server 2005.)
It's not quite that simple -- but almost. We're going to look at the REPLACE funtion. Replace searches for certain characters in a string and replaces them with other characters. So this statement:
SELECT Replace('SQLTeam.com Rocks!', 'Rocks', 'Rolls')
will return
SQLTeam.com Rolls!
REPLACE searches the the first string for any occurance of the the second string and replaces it with the third string. You can also do replacements of different sizes. For example,
SELECT Replace('SQLTeam.com Rocks!', 'Rocks', 'is cool')
gives us
SQLTeam.com is cool!
I replaced a five character string with a seven character string with no problem. If the string isn't found, no changes will be made.
SELECT Replace('SQLTeam.com Rocks!', 'Yak', 'Tibetan bison')
returns exactly what we started with which is
SQLTeam.com Rocks!
If it doesn't find anything to change it just returns the string unchanged. You can use REPLACE in an UPDATE statement. Using the pubs database we could write:
Update authors
Set city = replace(city, 'Salt', 'Olympic')
There were two authors that had "Salt Lake City" in the CITY field. Now that field holds "Olympic Lake City" for those two authors. The CITY field is unchanged for all the other authors.
That should help you write your UPDATE statement Andre. Good luck!
