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
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Multiple REPLACE statements on one column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 09/23/2005 :  07:38:46  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Toutski writes "Can you apply more than one replace statement to a single column in an SQL table?

At the moment I have something like this:


select replace(frequency,'A','Weekly')as freq1, replace(frequency,'B','Twice Weekly') as freq1, replace(frequency,'F','Fortnightly')as freq1 from tw_product_by_customer

but this returns a result set with 3 columns called freq1 and I wish to replace the values and keep them in the one column.


Can this be done?"

robvolk
Most Valuable Yak

USA
15678 Posts

Posted - 09/23/2005 :  07:41:29  Show Profile  Visit robvolk's Homepage  Reply with Quote
Yes, you just have to nest them:

select replace(
replace(
replace(frequency,'A','Weekly')
, 'B','Twice Weekly')

, 'F','Fortnightly')
as freq1
from tw_product_by_customer
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 09/23/2005 :  07:42:45  Show Profile  Visit ditch's Homepage  Reply with Quote
select replace(replace(replace(frequency,'A','Weekly'),'B','Twice Weekly'),'F','Fortnightly')as freq1 from tw_product_by_customer

Duane.


edit: ooops!!! beten to it by rob

Edited by - ditch on 09/23/2005 07:44:01
Go to Top of Page

robvolk
Most Valuable Yak

USA
15678 Posts

Posted - 09/23/2005 :  07:43:55  Show Profile  Visit robvolk's Homepage  Reply with Quote
Well yeah, that works too, but it doesn't have the nice colors.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 09/23/2005 :  07:45:07  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Rob, I think you like more colors

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 09/23/2005 :  07:45:47  Show Profile  Visit ditch's Homepage  Reply with Quote
quote:
Originally posted by robvolk

Well yeah, that works too, but it doesn't have the nice colors.



not only must I learn to type faster - I must also learn to spell (beten? beaten) - and I must start using pretty colours too

Duane.
Go to Top of Page

HomerJ
Starting Member

USA
21 Posts

Posted - 03/10/2009 :  15:59:03  Show Profile  Reply with Quote
Sorry to bring this thread back from the dead, but as a followup:

I'm doing some cleanup, and need to do multiple replaces. In fact, the list of replaceable strings continue to grow as we do analysis. Is there a way to base a replace statement off of another table of values? I'm thinking I could just keep adding values to the table and re-run the statement to continually update the field.
Go to Top of Page

tosscrosby
Aged Yak Warrior

USA
676 Posts

Posted - 03/10/2009 :  17:11:33  Show Profile  Reply with Quote
Never re-open a thread that's been dormant for 3 1/2 years. A new thread will possibly yield quicker responses. Anyway, show us what you have with respect to data structure and TSQL statements just to get us started. Sample data in the form of inserts is always helpful as well.

Terry

-- Procrastinate now!
Go to Top of Page

HomerJ
Starting Member

USA
21 Posts

Posted - 03/12/2009 :  15:27:13  Show Profile  Reply with Quote
Here's the current script:

SELECT RDC.Property_Name, RDC.ZIP,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([Property_Name],'The ',''),',',''),'''',''),'Apartments',''),'Apartment',''),'APTS','')
& [ZIP] AS TRIMNAMEZIP
FROM RDC
WHERE LEVEL = 1;

The replace list just continues to grow, and the replace sequence becomes harder and hard to order. I'm hoping there's a way to reference a second table, potentially with a value to reference the replace-order, so as I'm asked for more text replacements, I can just add them to the table and keep rolling.
Go to Top of Page

Grizzly
Starting Member

1 Posts

Posted - 04/01/2009 :  08:17:00  Show Profile  Reply with Quote
Hi HomerJ,
try something like this:

CREATE TABLE X_REPLACEMENTS (
string NVARCHAR(100),
replacement NVARCHAR(100));

INSERT INTO X_REPLACEMENTS VALUES ('abc','123');
INSERT INTO X_REPLACEMENTS VALUES ('xxx','666');

DECLARE @v_str NVARCHAR(1000);
SET @v_str = 'abc..xabc xxx xyz';

SELECT @v_str = REPLACE(@v_str,string,replacement)
FROM X_REPLACEMENTS;

PRINT @v_str;


--------------------
This works fine if no string in X_REPLACEMENTS is a substring of another string in this table.
In case if you want for example to replace both strings "MAX" (with "111" eg.) and "MAXIMUM" (with "222" eg.), it is good idea to sort strings by length desc...so "MAXIMUM" will be always replaced with "222" and not with "111IMUM". In this case, the SELECT replacement may look like this:

SELECT @v_str = REPLACE(@v_str,string,replacement)
FROM (SELECT TOP 99999 * -- note: seems like TOP 100 PERCENT does not work properly here for me, no idea why
FROM X_REPLACEMENTS cef
ORDER BY LEN(string) DESC) x;


Grizzly
Go to Top of Page

buswala
Starting Member

Pakistan
3 Posts

Posted - 12/29/2012 :  05:07:12  Show Profile  Reply with Quote
Hi, i am new to sql, i have one table having a column of values of g0,g1,g2,g3....g54. and second column values is m0,m1,m2....m27. i want to replace the g0 g1 g2 ....g54 and m1,m2,m3....m27 with different values. How would i do.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/29/2012 :  10:19:07  Show Profile  Reply with Quote
You can use Case inside Replace Statement.

Like this

Replace(Column,Case When Column = .......End,Case When Column = .......End)
Go to Top of Page

buswala
Starting Member

Pakistan
3 Posts

Posted - 01/07/2013 :  03:31:19  Show Profile  Reply with Quote
Dear Sodeep, i didn't understand. Just take a look again.
I have a table name newsletter and a coloumn name Catg having the values g0,g1,g2,g3 ....g54.
i want to replace the above values of g0 with general, g1 with medical, g2 with social, g3 with science. kindly write the complete query to replace all the values with my giving values in a single row. For example..
Table name newsletter
Column (Email)----------Column (Catg)----------Column (Catm)
abc@gmail.com-----------g0,g1,g2,g3,g4,g5------m0,m1,m2,m3

It must be like
Table name newsletter
Column (Email)----------Column (Catg)-----------------------Column (Catm)
abc@gmail.com-----------general,medical,social,science------biography,politcs,health
Go to Top of Page

decknail
Starting Member

USA
1 Posts

Posted - 01/21/2013 :  06:49:24  Show Profile  Reply with Quote
I would like to know how to create RowNum column in SQL server?

unspammed

Edited by - decknail on 01/21/2013 06:50:11
Go to Top of Page

buswala
Starting Member

Pakistan
3 Posts

Posted - 01/22/2013 :  00:50:26  Show Profile  Reply with Quote
Hi, i am new to sql, i have one table having a column of values of g0,g1,g2,g3....g54. and second column values is m0,m1,m2....m27. i want to replace the g0 g1 g2 ....g54 and m1,m2,m3....m27 with different values. How would i do.

I have a table name newsletter and a coloumn name Catg having the values g0,g1,g2,g3 ....g54.
i want to replace the above values of g0 with general, g1 with medical, g2 with social, g3 with science. kindly write the complete query to replace all the values with my giving values in a single row. For example..
Table name newsletter
Column (Email)----------Column (Catg)----------Column (Catm)
abc@gmail.com-----------g0,g1,g2,g3,g4,g5------m0,m1,m2,m3

It must be like
Table name newsletter
Column (Email)----------Column (Catg)-----------------------Column (Catm)
abc@gmail.com-----------general,medical,social,science------biography,politcs,health
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.22 seconds. Powered By: Snitz Forums 2000