SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

AskSQLTeam
Ask SQLTeam Question

USA
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 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 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

jbkayne
Posting Yak Master

100 Posts

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

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



Go to Top of Page

gheeren
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

robvolk
Most Valuable Yak

USA
15681 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

travisbrown
Starting Member

Canada
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

jcelko
Esteemed SQL Purist

USA
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(
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
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
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
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
Go to Top of Page

azizi47
Starting Member

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

Thanks.

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

http://www.thecurrentaffairs.com
http://www.traveljin.com
Go to Top of Page

amz.mrakhmanov
Starting Member

1 Posts

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

Blonk4
Starting Member

Netherlands
1 Posts

Posted - 01/14/2011 :  04:38:51  Show Profile  Reply with Quote
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
Go to Top of Page

robvolk
Most Valuable Yak

USA
15681 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  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000