Article: Using REPLACE in an UPDATE statement

Please start any new threads on our new site at https://forums.sqlteam.com. 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

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-20 : 21:32:31

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 is probably not as simple as:

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

..or is it?"

Article Link.

vikram_khurana
Starting Member

1 Post

Posted - 2002-01-28 : 17:50:08

How do i use the function replace to change ;$;$ to a carriage return?

jbkayne
Posting Yak Master

100 Posts

Posted - 2002-01-28 : 17:54:06

Use CHAR(13)

i.e.
replace(string, ';$;$', char(13))

gheeren
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.

robvolk
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.

travisbrown
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

jcelko
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(
REPLACE(
...
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.

--CELKO--
Joe Celko, SQL Guru

AjarnMark
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!

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-09-05 : 08:54:09

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:


REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
  LTRIM(TestValue),
  '1','0'), '2','0'), '3','0'), '4','0'),'5','0'),
  '6','0'), '7','0'), '8','0'), '9','0'),
  '00','0'),'00','0'),'00','0'),'00','0'),'00','0'),'00','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

azizi47
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.

Thanks.

When it comes to Professional - Love it or Leave it.

http://www.thecurrentaffairs.com
http://www.traveljin.com

amz.mrakhmanov
Starting Member

1 Post

Posted - 2010-11-29 : 16:29:26

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?

Blonk4
Starting Member

1 Post

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

Hi,

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.

regards,
Allistair

robvolk
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources