Multiple REPLACE statements on one column

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

SQL Server 2000 Forums

SQL Server Development (2000)

Multiple REPLACE statements on one column

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-09-23 : 07:38:46

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

15732 Posts

Posted - 2005-09-23 : 07:41:29

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

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-23 : 07:42:45

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-09-23 : 07:43:55

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-23 : 07:45:07

Rob, I think you like more colors

Madhivanan

Failing to plan is Planning to fail

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-23 : 07:45:47

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.

HomerJ
Starting Member

21 Posts

Posted - 2009-03-10 : 15:59:03

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.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-03-10 : 17:11:33

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!

HomerJ
Starting Member

21 Posts

Posted - 2009-03-12 : 15:27:13

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.

Grizzly
Starting Member

1 Post

Posted - 2009-04-01 : 08:17:00

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

buswala
Starting Member

3 Posts

Posted - 2012-12-29 : 05:07:12

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-29 : 10:19:07

You can use Case inside Replace Statement.

Like this

Replace(Column,Case When Column = .......End,Case When Column = .......End)

buswala
Starting Member

3 Posts

Posted - 2013-01-07 : 03:31:19

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

decknail
Starting Member

1 Post

Posted - 2013-01-21 : 06:49:24

I would like to know how to create RowNum column in SQL server?

unspammed

buswala
Starting Member

3 Posts

Posted - 2013-01-22 : 00:50:26

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources