Using REPLACE in an UPDATE statement

By Bill Graziano on 31 March 2010 | 12 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.

Discuss this article: 12 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Related Articles

SQL Server 2005: Using OVER() with Aggregate Functions (21 May 2007)

DATEDIFF Function Demystified (20 March 2007)

Using the PARSENAME function to split delimited data (10 November 2003)

Intro to User Defined Functions (Updated) (8 January 2001)

String Functions: Incrementing a Number in a Char (27 November 2000)

User Defined Functions (12 October 2000)

Creating a Sequential Record Number field (25 September 2000)

Using ISNULL (25 August 2000)

Other Recent Forum Posts

some help pls.... (0 Replies)

Calculated Field (15 Replies)

exclude data from query (3 Replies)

Delete Files from directory (5 Replies)

Need to qualify the results of a query (3 Replies)

SQL 2008 R2 Service Pack questions (0 Replies)

ORA-01795 - max. 1000 in a list (3 Replies)

Record count error? (3 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -