| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
 Posted - 09/23/2005 : 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
USA
15557 Posts |
Posted - 09/23/2005 : 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
Flowing Fount of Yak Knowledge
South Africa
1416 Posts |
Posted - 09/23/2005 : 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  |
Edited by - ditch on 09/23/2005 07:44:01 |
|
|
|
robvolk
Most Valuable Yak
USA
15557 Posts |
Posted - 09/23/2005 : 07:43:55
|
Well yeah, that works too, but it doesn't have the nice colors.  |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 09/23/2005 : 07:45:07
|
Rob, I think you like more colors
Madhivanan
Failing to plan is Planning to fail |
|
|
|
ditch
Flowing Fount of Yak Knowledge
South Africa
1416 Posts |
Posted - 09/23/2005 : 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
USA
21 Posts |
Posted - 03/10/2009 : 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
USA
676 Posts |
Posted - 03/10/2009 : 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
USA
21 Posts |
Posted - 03/12/2009 : 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 Posts |
Posted - 04/01/2009 : 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
Pakistan
3 Posts |
Posted - 12/29/2012 : 05:07:12
|
| 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. |
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/29/2012 : 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
Pakistan
3 Posts |
Posted - 01/07/2013 : 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
USA
1 Posts |
Posted - 01/21/2013 : 06:49:24
|
I would like to know how to create RowNum column in SQL server?
unspammed |
Edited by - decknail on 01/21/2013 06:50:11 |
|
|
|
buswala
Starting Member
Pakistan
3 Posts |
Posted - 01/22/2013 : 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
|
|
|
| |
Topic |
|
|
|
Multiple REPLACE statements on one column
