Using REPLACE in an UPDATE statement
By Bill Graziano
on 31 March 2010
| 9 Comments
| Tags: UPDATE, Functions
This article covers using the REPLACE function to selectively replace text inside a string in SQL Server. The REPLACE function is easy to use and very handy with an UPDATE statment.
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 dbo.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.
A more common approach is to use this in conjuntion with a WHERE clause
like this:
UPDATE dbo.authors
SET city = replace(city, 'Salt', 'Olympic')
WHERE city LIKE 'Salt%';
This only affects the rows that start with 'Salt'.
Note: This article was originally published in January 2002 and has been
updated through SQL Server 2008 R2.
