Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Multiple replace on 1 column in select statement. Expand / Collapse
Author
Message
Posted Thursday, October 1, 2009 6:38 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 30, 2012 7:53 PM
Points: 519, Visits: 324
Hello! Haven't posted in awhile, but I am kinda stumped... maybe it is SQL writer's block. I cant figure out the most logical way to accomplish a multiple replace. For example what I would like to do is:

Select replace(new_header,'#firstname#', ereb.new_firstname)

BUT there are about 10 other strings I would like to replace from this same column before returning the output. If I just put multiple replace statements, I get multiple results of course. Is there an easy syntax out there that I am missing to replace multiple items from a string with DIFFERENT values? Thanks for any help guys.
Post #796273
Posted Thursday, October 1, 2009 7:31 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 30, 2012 7:53 PM
Points: 519, Visits: 324
Nevermind. Had a senior moment (which is weird since I am only 26). I just went with:

 replace(replace(new_header,'#firstname#',firstname),'#lastname',lastname)

And added the replace statements for all of my string, and it seemed to work.
Post #796314
Posted Wednesday, September 22, 2010 4:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 1:30 AM
Points: 161, Visits: 345
Yeah, not nice solution. Anyway, I'm stuck too to do such.

Something looking pretty useful was posted on other forum:


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;


Work nice, anyway it would be nice to use something like this in update or even in select query (meaning updating data in table1 using replacements from table2). Any idea how to accomplish this?
Post #991006
Posted Wednesday, September 22, 2010 4:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:56 AM
Points: 7,120, Visits: 13,491
vevoda.ulath (9/22/2010)
Yeah, not nice solution. Anyway, I'm stuck too to do such.

Something looking pretty useful was posted on other forum:


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;


Work nice, anyway it would be nice to use something like this in update or even in select query (meaning updating data in table1 using replacements from table2). Any idea how to accomplish this?

Can you confirm which version of SQL Server you are using? This is a 2k5 thread.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #991013
Posted Wednesday, September 22, 2010 5:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 1:30 AM
Points: 161, Visits: 345
Well, primary is question directed to SQL 2005. I'll be more specific:

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
IF OBJECT_ID('TempDB..#replacements','U') IS NOT NULL
DROP TABLE #replacements

--===== Create the test table with
CREATE TABLE #mytable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
LongString nvarchar(max)
)

--===== Create table with replacements
CREATE TABLE #replacements
(
code nvarchar(55)
,replacement nvarchar(255)
)


--===== Add sample data
INSERT INTO #mytable (LongString)
select 'code01 something else code02' union all
select 'code02 code02 aacode01'

INSERT INTO #replacements
select 'code01', 'XXXX' union all
select 'code02', 'YYYY'

Well, my intention is to write query for select data from #myTable using replacements from #replacements table, i.e. desired output is


XXXX something else YYYY
YYYY YYYY aaXXXX



Hope it's clear, thx in advance for any suggestion.

Note: I really want to avoid using cursor, while loops, etc. And I really won't use replace(replace(replace(replace(.... query, imagine that #replacements table got 100 records, which could be changed time to time.
Post #991025
Posted Wednesday, September 22, 2010 6:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:56 AM
Points: 7,120, Visits: 13,491
vevoda.ulath (9/22/2010)

...
Well, my intention is to write query for select data from #myTable using replacements from #replacements table, i.e. desired output is


XXXX something else YYYY
YYYY YYYY aaXXXX



Hope it's clear, thx in advance for any suggestion.

Note: I really want to avoid using cursor, while loops, etc. And I really won't use replace(replace(replace(replace(.... query, imagine that #replacements table got 100 records, which could be changed time to time.


Here you go. No loops, cursors or even (incredibly fast) nested REPLACE:

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
IF OBJECT_ID('TempDB..#replacements','U') IS NOT NULL
DROP TABLE #replacements

--===== Create the test table with
CREATE TABLE #mytable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
LongString nvarchar(max)
)

--===== Create table with replacements
CREATE TABLE #replacements
(
code nvarchar(55)
,replacement nvarchar(255)
)


--===== Add sample data
INSERT INTO #mytable (LongString)
select 'code01 something else code02' union all
select 'code02 code02 aacode01'

INSERT INTO #replacements
select 'code01', 'XXXX' union all
select 'code02', 'YYYY'


-- solution
;WITH Tally AS (SELECT top 100 n = ROW_NUMBER() OVER(ORDER BY [name]) from master.dbo.syscolumns),
NormalisedData AS (
SELECT m.ID,
WordID = ROW_NUMBER() OVER(PARTITION BY m.ID ORDER BY t.n),
m.longstring,
word = SUBSTRING(m.longstring, t.n,
(ISNULL(NULLIF(CHARINDEX(' ', SUBSTRING(m.longstring, t.n, LEN(m.LongString)-t.n)), 0),
LEN(SUBSTRING(m.longstring, t.n, LEN(m.LongString)-t.n))+2)) - 1)
FROM #mytable m
INNER JOIN Tally t ON SUBSTRING(' ' + m.LongString, t.n, 1) = ' '
WHERE t.n <= LEN(m.LongString)
),
ReplacedData AS (
SELECT d.ID, d.WordID, d.LongString, NewWord = ISNULL(r.replacement, d.word)
FROM NormalisedData d
LEFT JOIN #replacements r ON r.code = d.word
)
SELECT d.ID, OldString = d.LongString,
NewString = (
SELECT ' ' + NewWord
FROM ReplacedData r
WHERE r.ID = d.ID
ORDER BY ID, WordID
FOR XML PATH(''))
FROM ReplacedData d
GROUP BY d.ID, d.LongString








“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #991068
Posted Wednesday, September 22, 2010 7:10 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 30, 2012 7:53 PM
Points: 519, Visits: 324
Can someone please explain the drawback of having a nested replace?
Post #991120
Posted Wednesday, September 22, 2010 7:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:56 AM
Points: 7,120, Visits: 13,491
Mike Menser (9/22/2010)
Can someone please explain the drawback of having a nested replace?


It can be awkward to get all those pesky commas and brackets in the right place? Small price to pay for top performance. Nested REPLACE's are super-speedy.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #991133
Posted Wednesday, September 22, 2010 7:39 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 30, 2012 7:53 PM
Points: 519, Visits: 324
I would think it would be more taxing at the server to write a loop, or create a temp table and insert all the values and then do a gigantic replace at the end rather than just doing a nested replace. I can see where it would be tedious, but it seems to functioning well at 14 deep for me, so since I dont see any real performance drawbacks I think I will keep it.
Post #991157
Posted Wednesday, September 22, 2010 7:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:56 AM
Points: 7,120, Visits: 13,491
Mike Menser (9/22/2010)
I would think it would be more taxing at the server to write a loop, or create a temp table and insert all the values and then do a gigantic replace at the end rather than just doing a nested replace. I can see where it would be tedious, but it seems to functioning well at 14 deep for me, so since I dont see any real performance drawbacks I think I will keep it.


Your call Mike, and it looks like the right one to me

The problem posed by vevoda.ulath looks quite different. What makes it so is the requirement for multiple rows of a helper table to be involved on an operation on a single row of a target table.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #991171
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse