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!

Discuss this article: 8 Comments so far. Print this Article. This page has been read 233,373 times.

If you like this article you can sign up for our newsletter. We send it out each week that we post a new article. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Email ThisSubscribe to this feedKick itSave to del.icio.usView blog reactions

Related Articles

Creating a Sequential Record Number field (25 September 2000)

Other Recent Forum Posts

Using "LIKE" for may results (5 Replies)

Add Unique Constraint problem. (1 Reply)

Data Transformation Services in SQL Server 2005 (2 Replies)

database is 350 gb?? (2 Replies)

Ambiguous values returned (3 Replies)

Stay in Simple mode? (0 Replies)

Variable in stored procedure (1 Reply)

Mirroring & Snapshots? (0 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email:

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

SQL Server Jobs