Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multiple replace on 1 column in select statement.


Multiple replace on 1 column in select statement.

Author
Message
Mike Menser
Mike Menser
Mr or Mrs. 500
Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)

Group: General Forum Members
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.
Mike Menser
Mike Menser
Mr or Mrs. 500
Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)

Group: General Forum Members
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. :-P
vevoda.ulath
vevoda.ulath
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 396
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?
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9011 Visits: 19028
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
vevoda.ulath
vevoda.ulath
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 396
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.
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9011 Visits: 19028
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
Mike Menser
Mike Menser
Mr or Mrs. 500
Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)

Group: General Forum Members
Points: 519 Visits: 324
Can someone please explain the drawback of having a nested replace?
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9011 Visits: 19028
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
Mike Menser
Mike Menser
Mr or Mrs. 500
Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)

Group: General Forum Members
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. :-D
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9011 Visits: 19028
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. :-D


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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search