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.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Using REPLACE in an UPDATE statement
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ask SQLTeam Question

0 Posts

Posted - 01/20/2002 :  21:32:31  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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 Posts

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

Go to Top of Page

Posting Yak Master

100 Posts

Posted - 01/28/2002 :  17:54:06  Show Profile  Reply with Quote
Use CHAR(13)

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

Go to Top of Page

Starting Member

1 Posts

Posted - 01/28/2002 :  18:16:57  Show Profile  Reply with Quote
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 - 01/28/2002 :  18:41:25  Show Profile  Visit robvolk's Homepage  Reply with Quote
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 Posts

Posted - 02/08/2002 :  17:19:36  Show Profile  Reply with Quote
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 - 04/09/2002 :  13:39:10  Show Profile  Visit jcelko's Homepage  Reply with Quote
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 - 09/04/2002 :  19:18:33  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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

United Kingdom
1961 Posts

Posted - 09/05/2002 :  08:54:09  Show Profile  Reply with Quote
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 - 08/27/2010 :  17:31:25  Show Profile  Reply with Quote
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 Posts

Posted - 11/29/2010 :  16:29:26  Show Profile  Reply with Quote
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 Posts

Posted - 01/14/2011 :  04:38:51  Show Profile  Reply with Quote

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 - 01/14/2011 :  07:35:34  Show Profile  Visit robvolk's Homepage  Reply with Quote
Use the STUFF function to replace or remove characters in specific positions. It is documented in Books Online.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000