SQL Server – Using REPLACE in an UPDATE Statement

This blog covers using the REPLACE function to selectively replace text inside a string in SQL Server. The REPLACE function is easy to use and also very handy with an UPDATE statement.

Replace searches for certain characters in a string and replaces them with other characters. For example, this statement:

SELECT Replace(‘Directions trains the best’, ‘best’, ‘pros’)

will return:

Directions trains the pros

REPLACE searches the first string for any occurrence of the second string and replaces it with the third string. You can also do replacements of different sizes. For example,

SELECT Replace(‘Directions trains the best!’, ‘the best’, ‘in the corporate environment’)

gives us:

Directions trains in the corporate environment!

I replaced an eight character string with a twenty-seven character string with no problem. If the string isn’t found, no changes will be made.

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, ‘Oak Brook’, ‘Chicago’);

There were two authors that had “City of Oak Brook” in the CITY field. Now that field holds “City of Chicago” for those two authors. The CITY field is unchanged for all the other authors.

A more common approach is to use this in conjunction with a WHERE clause like this:

UPDATE dbo.authors

SET city = replace(city, ‘Oak Brook’, ‘Chicago’)

WHERE city LIKE ‘Oak%’;

This only affects the rows that start with ‘Oak’.

For more Microsoft Technical Training information visit www.directionstraining.com or call 1-855-575-8900.