October 1, 2009 at 6:38 am
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.
October 1, 2009 at 7:31 am
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. 😛
September 22, 2010 at 4:32 am
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?
September 22, 2010 at 4:50 am
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.
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
September 22, 2010 at 5:16 am
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.
September 22, 2010 at 6:18 am
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
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
September 22, 2010 at 7:10 am
Can someone please explain the drawback of having a nested replace?
September 22, 2010 at 7:20 am
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.
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
September 22, 2010 at 7:39 am
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. 😀
September 22, 2010 at 7:46 am
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.
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
September 22, 2010 at 9:14 am
Chris Morris-439714 (9/22/2010)
vevoda.ulath (9/22/2010)
Here you go. No loops, cursors or even (incredibly fast) nested REPLACE:
..
..
.
Wow, you rock!!! Just getting into tally tables and will take a deeper look on them 'cause they look great.
Thanks again, i appreciate it.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply