Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 Article Discussion
 Article: Using REPLACE in an UPDATE statement

Author  Topic 

Ask SQLTeam Question

0 Posts

Posted - 2002-01-20 : 21:32:31
Andre writes "Howdy, I am converting an asp application into 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 is probably not as simple as:

Update sid_tbl.sidnamn Set '.asp' = '.aspx';

..or is it?"

Article Link.

Starting Member

1 Post

Posted - 2002-01-28 : 17:50:08
How do i use the function replace to change ;$;$ to a carriage return?

Go to Top of Page

Posting Yak Master

100 Posts

Posted - 2002-01-28 : 17:54:06
Use CHAR(13)

replace(string, ';$;$', char(13))

Go to Top of Page

Starting Member

1 Post

Posted - 2002-01-28 : 18:16:57
Any ideas on how to use this on a text column that has data size greater than 8000? Replace() does not like text data types and 8000 is the biggest size for varchar (on SQL 7.0 at least) so convert() does not help.

Go to Top of Page

Most Valuable Yak

15732 Posts

Posted - 2002-01-28 : 18:41:25
You'd have to use UPDATETEXT for it. I don't know for sure if CHARINDEX() works with text columns, but PATINDEX() does. That would help you search the text column for a string.

You'd have to write a couple of loops, maybe even a (shudder! NO! NO!) cursor if you need to process multiple rows.

Go to Top of Page

Starting Member

1 Post

Posted - 2002-02-08 : 17:19:36
I've used this:

UPDATE dbo.Table1
SET test1 = REPLACE(test1, 'book', 'and')

'dbo.Table1 = table name
'test1 = column name to be affected
'expression 1 = column name where to find exp 2
'expression 2 = string to replace
'expression 3 = replacement string

Go to Top of Page

Esteemed SQL Purist

547 Posts

Posted - 2002-04-09 : 13:39:10
Another trick with REPLACE() is to nest it; you can go to 32 levels deep, so you can write things like:

REPLACE(foobar, 'Z', 'z'),
'B', 'b'),
'A', 'a')

This is obviously a bad way to implement LOWER(), but you can use it to remove dashes, commas, embedded blanks and other punctuation in a string.

Joe Celko, SQL Guru
Go to Top of Page

SQL Slashing Gunting Master

3246 Posts

Posted - 2002-09-04 : 19:18:33
Graz, nice article! Unfortunately I didn't run across it until I had already written my own code using substring and charindex... Oh well, my version worked for the 1-off situation I had, and now I know better for next time.

Keep 'em coming!
Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-09-05 : 08:54:09
you can go to 32 levels deep

The 32 level limit only applies to user-defined functions: you can nest it about 490 levels before it complains (7.0 and 2000).
Here's something I've used for trying to identify what was in a varchar column that mostly contained numeric values but had had some additional forms:

'1','0'), '2','0'), '3','0'), '4','0'),'5','0'),
'6','0'), '7','0'), '8','0'), '9','0'),

The number of '00' -> '0' replacements is a little arbitrary, but the strings I was dealing with didn't have substrings of more than 64 contiguous digits.

Oops! Misread the date and thought Joe posted that item yesterday

Edited by - Arnold Fribble on 09/05/2002 08:57:39
Go to Top of Page

Starting Member

2 Posts

Posted - 2010-08-27 : 17:31:25
i am thinking about migrating from sql 2005 to sql 2008, these articles are really helping me.


When it comes to Professional - Love it or Leave it.
Go to Top of Page

Starting Member

1 Post

Posted - 2010-11-29 : 16:29:26
Note: This article was originally published in January 2002 and has been updated through SQL Server 2008 R2.

What was changed in the article specifically for the SQL Server 2008 R2?
Go to Top of Page

Starting Member

1 Post

Posted - 2011-01-14 : 04:38:51

Is it possible to use REPLACE to replace a set of letters in a field.
For example;
I need to replace the first 2 letters of every item in column 1 and also the 5th and 6th letters in column 6.

Go to Top of Page

Most Valuable Yak

15732 Posts

Posted - 2011-01-14 : 07:35:34
Use the STUFF function to replace or remove characters in specific positions. It is documented in Books Online.
Go to Top of Page

- Advertisement -