January 22, 2013 at 1:16 am
Here's my variation to do a dynamic replace of elements from one delimited string by another. It's really just a variation of DelimitedSplit8K. Just out of curiosity I ran it against a version of Jeff Moden's test harness and it seems to perform well even though it's a multi-statement tvf. I've attached the testing code in case anyone wants to try it themselves or test any new variations. (Sorry dwain.c, I didn't test your code, but if you or anyone else cares enough it wouldn't be that hard to test it with the attached testing code.)
Sample data and usage example:
DECLARE
@strTarget VARCHAR(8000)
,@str1 VARCHAR(8000)
,@str2 VARCHAR(8000)
SET @strTarget = 'Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat.'
SET @str1 = 'lorem|ipsum|dolor|diam|tincidunt|laoreet|aliquam'
SET @str2 = '100|200|300|400|500|600|700|800'
SELECT ItemResult FROM dbo.NestedReplace(@strTarget,@str1,'|',@str2,'|')
Output of the above sample data:
100 200 300 sit amet, consectetuer adipiscing elit, sed 400 nonummy nibh euismod 500 ut 600 300e magna 700 erat volutpat.
CREATE FUNCTION dbo.NestedReplace
(
@pTarget VARCHAR(8000)
,@pString1 VARCHAR(8000)
,@pDelimiter1 CHAR(1)
,@pString2 VARCHAR(8000)
,@pDelimiter2 CHAR(1)
)
RETURNS
@NestedReplace TABLE
(
[ItemNumber] [bigint] NOT NULL,
[Item1] [varchar](8000) NULL,
[Item2] [varchar](8000) NULL,
[ItemResult] [varchar](8000) NULL,
PRIMARY KEY (ItemNumber))
WITH SCHEMABINDING
AS
BEGIN
DECLARE
@strItem1 VARCHAR(8000)
,@strItem2 VARCHAR(8000)
,@strResult VARCHAR(8000)
,@intMaxItemNumber INT
,@intCounter INT
INSERT INTO @NestedReplace
SELECT
ds1.ItemNumber
,ds1.Item AS Item1
,ds2.Item AS Item2
,NULL AS ItemResult
FROM
dbo.DelimitedSplit8K(@pString1,@pDelimiter1) AS ds1
INNER JOIN
dbo.DelimitedSplit8K(@pString2,@pDelimiter2) AS ds2
ON ds1.ItemNumber = ds2.ItemNumber
SELECT @intMaxItemNumber = MAX(ItemNumber) FROM @NestedReplace
SET @intCounter = 1
UPDATE @NestedReplace
SET ItemResult = @pTarget
WHERE ItemNumber = 1
WHILE @intCounter <= @intMaxItemNumber
BEGIN
SELECT
@strResult = ItemResult
FROM
@NestedReplace
WHERE
ItemNumber = 1
SELECT
@strItem1 = Item1
,@strItem2 = Item2
FROM
@NestedReplace
WHERE
ItemNumber = @intCounter
UPDATE @NestedReplace
SET
ItemResult = REPLACE(@strResult,@strItem1,@strItem2)
,Item1 = NULL
,Item2 = NULL
WHERE ItemNumber = 1
SET @intCounter = @intCounter + 1
END
DELETE FROM @NestedReplace
WHERE ItemNumber > 1
RETURN
END
GO
January 22, 2013 at 4:18 am
Steven Willis (1/22/2013)
Sorry dwain.c, I didn't test your code, but if you or anyone else cares enough it wouldn't be that hard to test it with the attached testing code.
No apologies necessary! Time is a limited and valuable commodity. If I had it I'd probably indulge (not that I think the rCTE iTVF I wrote would win) but alas I don't at the moment.
Filed for future reference though. 😀
EditOh and by the way. A minor improvement possible to both of our codes is to add:
COLLATE Latin1_General_BIN
to the REPLACEs.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 22, 2013 at 5:37 am
Can anyone join in?
Here's an efficient dynamic SQL version and a rCTE version. The rCTE version updates a million rows in about 30 seconds, the dynamic SQL version appears to be a little faster:
------------------------------------------------------------
-- Recursive CTE method
------------------------------------------------------------
-- sample data
DROP TABLE #UpdateTarget
CREATE TABLE #UpdateTarget (strTarget VARCHAR(8000))
INSERT INTO #UpdateTarget (strTarget)
SELECT d.strTarget
FROM (
SELECT strTarget = 'Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat.'
UNION ALL SELECT 'A second row, nothing more'
) d
CROSS APPLY (SELECT TOP 500000 n = 1 FROM sys.columns a, sys.columns b, sys.columns c) x
UPDATE t SET strTarget = mr.OutputString
FROM #UpdateTarget t
CROSS APPLY dbo.IF_MultipleReplace (strTarget) mr
SELECT * FROM #UpdateTarget
GO
------------------------------------------------------------
-- Dynamic SQL method
------------------------------------------------------------
-- Sample table
DROP TABLE #UpdateTarget
CREATE TABLE #UpdateTarget (strTarget VARCHAR(8000))
INSERT INTO #UpdateTarget (strTarget)
SELECT d.strTarget
FROM (
SELECT strTarget = 'Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat.'
UNION ALL SELECT 'A second row, nothing more'
) d
CROSS APPLY (SELECT TOP 500000 n = 1 FROM sys.columns a, sys.columns b, sys.columns c) x
-- Mapping table containing values to be replaced and replacement value
DECLARE @MappingTable TABLE (
Seq INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Oldval VARCHAR(15),
Newval VARCHAR(15))
INSERT INTO @MappingTable (Oldval, Newval)
SELECT 'Lorem', '100' UNION ALL
SELECT 'ipsum', '200' UNION ALL
SELECT 'dolor', '300' UNION ALL
SELECT 'diam', '400' UNION ALL
SELECT 'tincidunt', '500' UNION ALL
SELECT 'laoreet', '600' UNION ALL
SELECT 'aliquam', '700' UNION ALL
SELECT 'not in string', '800'
DECLARE @SQL1 VARCHAR(8000), @SQL2 VARCHAR(8000), @SQL3 VARCHAR(8000)
SELECT
@SQL1 = ISNULL(@SQL1,'') + 'REPLACE(',
@SQL2 = ISNULL(@SQL2,'') + ', ''' + Oldval + ''', ''' + Newval + ''')'
FROM @MappingTable m
SET @SQL3 = 'UPDATE #UpdateTarget SET strTarget = ' + @SQL1 + ' strTarget ' + ' COLLATE LATIN1_GENERAL_BIN' + @SQL2
PRINT @SQL3
EXECUTE(@SQL3)
SELECT * FROM #UpdateTarget
GO
------------------------------------------------------------
-- Function definition for Dynamic SQL method
------------------------------------------------------------
CREATE FUNCTION [dbo].[IF_MultipleReplace]
(
@strTarget VARCHAR(8000)
)
RETURNS TABLE
AS
RETURN
(
WITH
MappingTable (Seq, Oldval, Newval) AS (
SELECT 1, 'Lorem', '100' UNION ALL
SELECT 2, 'ipsum', '200' UNION ALL
SELECT 3, 'dolor', '300' UNION ALL
SELECT 4, 'diam', '400' UNION ALL
SELECT 5, 'tincidunt', '500' UNION ALL
SELECT 6, 'laoreet', '600' UNION ALL
SELECT 7, 'aliquam', '700' UNION ALL
SELECT 8, 'not in string', '800'),
Calculator AS (
SELECT m.Seq, OutputString = REPLACE(@strTarget COLLATE LATIN1_GENERAL_BIN, Oldval, Newval)
FROM MappingTable m
WHERE Seq = 1
UNION ALL
SELECT m.Seq, OutputString = REPLACE(c.OutputString, Oldval, Newval)
FROM Calculator c
INNER JOIN MappingTable m ON m.Seq = c.Seq+1
)
SELECT TOP 1 OutputString
FROM Calculator
ORDER BY Seq DESC
--WHERE Seq = 8 -- 3 times slower
)
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
January 22, 2013 at 6:21 am
Uh, just asking here but wouldn't it make sense to vary the strings and the replacements?
Or did I miss something?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 22, 2013 at 6:23 am
dwain.c (1/22/2013)
Uh, just asking here but wouldn't it make sense to vary the strings and the replacements?Or did I miss something?
Not often you miss something Dwain! You're right of course. I'll see if there's a nice sample script to test with 😉
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
January 22, 2013 at 9:39 am
ChrisM@Work (1/22/2013)
dwain.c (1/22/2013)
Uh, just asking here but wouldn't it make sense to vary the strings and the replacements?Or did I miss something?
Not often you miss something Dwain! You're right of course. I'll see if there's a nice sample script to test with 😉
I posted my variation of Jeff Moden's testing code as an attachment above. It generates two strings. One function splits the 'lorem ipsum' text into one delimited target string. (Obviously, any string would work here.) A second function generates an equal number of random alphanumeric characters which are used as the source for the replace function test. The number and size of the elements in these strings is progressively increased using Jeff's original method, then some metrics are collected and saved in a results table. To test these alternative functions simply requires swapping out the specific 'split/replace' function and providing the proper parameters. I think my next project may be turning the whole test script into one stored procedure.
Concerning the problem of a nested replace, someone wondered why anyone would need to do this. I agree that it's an odd requirement. But like the OP I get stuck with lots of imported data that I need to scrub before it is usable. For example, I recently had to import WordPress articles to SQL. Besides having to replace a large number of non-functional links I also need to search for and replace stray ISO characters than don't render in normal HTML. Anyone who has tried to import something from WordPress will know what a PITA this is.
So, I was intrigued by this post. I want to do a query that pulls out a string to be "scrubbed," pass it into a function, and replace all the known anomalies with proper text.
A real-world example might be something like this:
DECLARE
@strTarget VARCHAR(8000)
,@str1 VARCHAR(8000)
,@str2 VARCHAR(8000)
SELECT @strTarget = ArticleText FROM dbo.Articles WHERE ArticleID = 1
SET @str1 = 'badurl.com|strayISOchar|OldCompanyName'
SET @str2 = 'goodurl.com|some new char|NewCompanyName'
UPDATE
dbo.Articles
SET
ArticleText =
(
SELECT ItemResult
FROM dbo.NestedReplace(@strTarget,@str1,'|',@str2,'|')
WHERE ArticleID = 1
)
January 22, 2013 at 1:29 pm
Hi
If you change the format of the replacement strings to be a single string of pairs then you could create a stored procedure to do a dynamic sql like the following (I made it a select rather than an update).
CREATE PROCEDURE multiReplacement (
@columnName varchar(100),
@replacementPairs varchar(1000),
@tableName varchar(100),
@whereClause varchar(1000) = null
)
AS BEGIN
DECLARE @sql VARCHAR(3000) = 'SELECT '
+ REPLICATE('REPLACE(', 1 + (LEN(@replacementPairs) - LEN(REPLACE(@replacementPairs,';',''))))
+ @columnname + ', '''
+ REPLACE(REPLACE(@replacementPairs,'|',''','''),';','''),''')
+ ''') new_value FROM ' + @tablename
+ isnull(' WHERE ' + nullif(@whereClause,''),'');
EXEC (@sql);
END
GO
This requires a string containing replacement pairs original|new separated by ;
There is a couple of caveats of course.
You can't replace ;'s and |'s
Don't put a trailing ; in the string
I tested it with this
SELECT 'The quick brown fox jumped over the fence' item into #test_table;
DECLARE @replacementPairs varchar(50) = 'quick|slow;brown|green;fox|turtle;jumped|crawled;over|under';
EXEC multiReplacement @columnName = 'Item', @replacementPairs = @replacementPairs, @tablename = '#test_table';
DROP TABLE #test_table;
January 22, 2013 at 3:15 pm
Last night while tossing and turning and getting virtually no sleep I thought of a new approach to this problem, which I will try to code up and post soonest.
It may not be fastest one around (the jury is still out on whose is) but I think y'all will find it quite interesting.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 22, 2013 at 5:49 pm
Gentlemen,
Allow me to enter a new dog in this race! Perhaps you'll find the new approach amusing. I call it the Nested Replace by Quirky Update (NRxQU for short). In marketing speak, we could even say that its got a catchy name!
CREATE FUNCTION [dbo].[NestedReplaceXQU]
(
-- Input parameters
@in VARCHAR(8000),
@ff VARCHAR(8000),
@RR VARCHAR(8000),
@delim CHAR(1)
)
RETURNS @Results TABLE
-- Note CLUSTERED INDEX on ID to drive the QU
(ID INT PRIMARY KEY CLUSTERED, r VARCHAR(8000))
WITH SCHEMABINDING
AS
BEGIN
DECLARE @rc INT
-- Initialize the table from the strings to be replaced
INSERT INTO @Results
SELECT ItemNumber, Item
FROM dbo.DelimitedSplit8K(@ff, @delim)
SELECT @rc = @@ROWCOUNT
-- QU the strings in the table with the nested replacements
UPDATE a
SET @in = REPLACE(@in COLLATE Latin1_General_BIN, a.r, b.Item)
,r = @in
FROM @Results a
INNER JOIN (SELECT ItemNumber, Item FROM dbo.DelimitedSplit8K(@rr, @delim)) b
ON a.ID = b.ItemNumber
-- Delete all but the last row (the one we want to keep)
DELETE FROM @Results WHERE ID <> @rc
RETURN
END
Note that the 3 queries are all relatively simplistic.
To demo that it works:
DECLARE @mystring VARCHAR(8000) = 'aaa|bb|c|d|e|f|g|h'
DECLARE @mystringnew VARCHAR(8000) = '11|2|3|4444|5|6|7|8'
DECLARE @Delim CHAR(1) = '|'
DECLARE @String VARCHAR(8000) = 'aaabbcdefgh'
SELECT s=@String, r
FROM dbo.NestedReplaceXQU(@String, @mystring, @mystringnew, @Delim)
Produces these results:
s r
aaabbcdefgh 112344445678
Now of course comes the real test. How does it run? I've compared against my original solution and Steve Willis' with apologies to my good friend ChrisM and the others that came along after because I simply didn't have time to figure out how to include them.
While others may disagree with this approach, I'm loathe to spend the time to decipher the suggested test harness so I decided to build my own, thinking it may be quicker. Perhaps that means we now have 2 to try it with, if someone wants to come along later and run these tests.
The test harness:
CREATE TABLE #Strings
(Original VARCHAR(8000), ToReplace VARCHAR(8000), ReplaceWith VARCHAR(8000))
DECLARE @s-2 VARCHAR(8000), @r VARCHAR(8000)
;WITH Tally (n) AS (
SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns a CROSS JOIN sys.all_columns b),
Strings AS (
SELECT TOP 78 n
,s=SUBSTRING('abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz', n, 1)
FROM (SELECT TOP 78 n FROM Tally) a)
INSERT INTO #Strings
SELECT s=(
SELECT s + ''
FROM Strings c
ORDER BY b.n, NEWID()
FOR XML PATH(''))
,ToReplace='a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|w|x|y|z'
,ReplaceWith=REPLICATE(SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1 + ABS(CHECKSUM(NEWID())) % 26, 1),1 + ABS(CHECKSUM(NEWID())) % 5) + @Delim +
REPLICATE(SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1 + ABS(CHECKSUM(NEWID())) % 26, 1),1 + ABS(CHECKSUM(NEWID())) % 5) + @Delim +
REPLICATE(SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1 + ABS(CHECKSUM(NEWID())) % 26, 1),1 + ABS(CHECKSUM(NEWID())) % 5) + @Delim +
REPLICATE(SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1 + ABS(CHECKSUM(NEWID())) % 26, 1),1 + ABS(CHECKSUM(NEWID())) % 5) + @Delim +
REPLICATE(SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1 + ABS(CHECKSUM(NEWID())) % 26, 1),1 + ABS(CHECKSUM(NEWID())) % 5) + @Delim +
REPLICATE(SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1 + ABS(CHECKSUM(NEWID())) % 26, 1),1 + ABS(CHECKSUM(NEWID())) % 5) + @Delim +
REPLICATE(SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1 + ABS(CHECKSUM(NEWID())) % 26, 1),1 + ABS(CHECKSUM(NEWID())) % 5) + @Delim +
REPLICATE(SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1 + ABS(CHECKSUM(NEWID())) % 26, 1),1 + ABS(CHECKSUM(NEWID())) % 5) + @Delim +
REPLICATE(SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1 + ABS(CHECKSUM(NEWID())) % 26, 1),1 + ABS(CHECKSUM(NEWID())) % 5) + @Delim +
REPLICATE(SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1 + ABS(CHECKSUM(NEWID())) % 26, 1),1 + ABS(CHECKSUM(NEWID())) % 5) + @Delim +
REPLICATE(SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1 + ABS(CHECKSUM(NEWID())) % 26, 1),1 + ABS(CHECKSUM(NEWID())) % 5) + @Delim +
REPLICATE(SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1 + ABS(CHECKSUM(NEWID())) % 26, 1),1 + ABS(CHECKSUM(NEWID())) % 5) + @Delim +
REPLICATE(SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1 + ABS(CHECKSUM(NEWID())) % 26, 1),1 + ABS(CHECKSUM(NEWID())) % 5) + @Delim +
REPLICATE(SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1 + ABS(CHECKSUM(NEWID())) % 26, 1),1 + ABS(CHECKSUM(NEWID())) % 5) + @Delim +
REPLICATE(SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1 + ABS(CHECKSUM(NEWID())) % 26, 1),1 + ABS(CHECKSUM(NEWID())) % 5) + @Delim +
REPLICATE(SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1 + ABS(CHECKSUM(NEWID())) % 26, 1),1 + ABS(CHECKSUM(NEWID())) % 5) + @Delim +
REPLICATE(SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1 + ABS(CHECKSUM(NEWID())) % 26, 1),1 + ABS(CHECKSUM(NEWID())) % 5) + @Delim +
REPLICATE(SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1 + ABS(CHECKSUM(NEWID())) % 26, 1),1 + ABS(CHECKSUM(NEWID())) % 5) + @Delim +
REPLICATE(SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1 + ABS(CHECKSUM(NEWID())) % 26, 1),1 + ABS(CHECKSUM(NEWID())) % 5) + @Delim +
REPLICATE(SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1 + ABS(CHECKSUM(NEWID())) % 26, 1),1 + ABS(CHECKSUM(NEWID())) % 5) + @Delim +
REPLICATE(SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1 + ABS(CHECKSUM(NEWID())) % 26, 1),1 + ABS(CHECKSUM(NEWID())) % 5) + @Delim +
REPLICATE(SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1 + ABS(CHECKSUM(NEWID())) % 26, 1),1 + ABS(CHECKSUM(NEWID())) % 5) + @Delim +
REPLICATE(SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1 + ABS(CHECKSUM(NEWID())) % 26, 1),1 + ABS(CHECKSUM(NEWID())) % 5) + @Delim +
REPLICATE(SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1 + ABS(CHECKSUM(NEWID())) % 26, 1),1 + ABS(CHECKSUM(NEWID())) % 5) + @Delim +
REPLICATE(SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1 + ABS(CHECKSUM(NEWID())) % 26, 1),1 + ABS(CHECKSUM(NEWID())) % 5) + @Delim +
REPLICATE(SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1 + ABS(CHECKSUM(NEWID())) % 26, 1),1 + ABS(CHECKSUM(NEWID())) % 5)
FROM Strings a
CROSS APPLY Tally b
GROUP BY b.n
ORDER BY b.n
PRINT 'Dwain.C (NestedReplace)'
SET STATISTICS TIME ON
FROM #Strings
CROSS APPLY dbo.NestedReplace(Original, ToReplace, ReplaceWith, @Delim)
SET STATISTICS TIME OFF
PRINT 'Dwain.C (NRxQU)'
SET STATISTICS TIME ON
FROM #Strings
CROSS APPLY dbo.NestedReplaceXQU(Original, ToReplace, ReplaceWith, @Delim)
SET STATISTICS TIME OFF
PRINT 'Steven Willis (NestedReplaceSW)'
SET STATISTICS TIME ON
SELECT @s-2=Original, @r=ItemResult
FROM #Strings
CROSS APPLY dbo.NestedReplaceSW(Original,ToReplace,@Delim,ReplaceWith,@Delim)
SET STATISTICS TIME OFF
DROP TABLE #Strings
And finally the results (after several caching runs), with apologies to Steve as I had to rename your function and add in the COLLATE Latin1_General_BIN to make it comparable (that code below so you know I didn't cheat).
(1000 row(s) affected)
Dwain.C (NestedReplace)
SQL Server Execution Times:
CPU time = 1716 ms, elapsed time = 1739 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.
Dwain.C (NRxQU)
SQL Server Execution Times:
CPU time = 951 ms, elapsed time = 1054 ms.
Steven Willis (NestedReplaceSW)
SQL Server Execution Times:
CPU time = 1716 ms, elapsed time = 1832 ms.
Steve's revised code:
CREATE FUNCTION dbo.NestedReplaceSW
(
@pTarget VARCHAR(8000)
,@pString1 VARCHAR(8000)
,@pDelimiter1 CHAR(1)
,@pString2 VARCHAR(8000)
,@pDelimiter2 CHAR(1)
)
RETURNS
@NestedReplace TABLE
(
[ItemNumber] [bigint] NOT NULL,
[Item1] [varchar](8000) NULL,
[Item2] [varchar](8000) NULL,
[ItemResult] [varchar](8000) NULL,
PRIMARY KEY (ItemNumber))
WITH SCHEMABINDING
AS
BEGIN
DECLARE
@strItem1 VARCHAR(8000)
,@strItem2 VARCHAR(8000)
,@strResult VARCHAR(8000)
,@intMaxItemNumber INT
,@intCounter INT
INSERT INTO @NestedReplace
SELECT
ds1.ItemNumber
,ds1.Item AS Item1
,ds2.Item AS Item2
,NULL AS ItemResult
FROM
dbo.DelimitedSplit8K(@pString1,@pDelimiter1) AS ds1
INNER JOIN
dbo.DelimitedSplit8K(@pString2,@pDelimiter2) AS ds2
ON ds1.ItemNumber = ds2.ItemNumber
SELECT @intMaxItemNumber = MAX(ItemNumber) FROM @NestedReplace
SET @intCounter = 1
UPDATE @NestedReplace
SET ItemResult = @pTarget
WHERE ItemNumber = 1
WHILE @intCounter <= @intMaxItemNumber
BEGIN
SELECT
@strResult = ItemResult
FROM
@NestedReplace
WHERE
ItemNumber = 1
SELECT
@strItem1 = Item1
,@strItem2 = Item2
FROM
@NestedReplace
WHERE
ItemNumber = @intCounter
UPDATE @NestedReplace
SET
ItemResult = REPLACE(@strResult COLLATE Latin1_General_BIN,@strItem1,@strItem2)
,Item1 = NULL
,Item2 = NULL
WHERE ItemNumber = 1
SET @intCounter = @intCounter + 1
END
DELETE FROM @NestedReplace
WHERE ItemNumber > 1
RETURN
END
Come on Chris - your turn to show us what yours can do!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 22, 2013 at 5:57 pm
Ooops, sorry. Almost forgot. I did have to make a couple of minor changes to my original NestedReplace FUNCTION so here's that code also:
CREATE FUNCTION [dbo].[NestedReplace]
(
-- Add the parameters for the function here
@in VARCHAR(8000),
@ff VARCHAR(8000),
@RR VARCHAR(8000),
@delim CHAR(1)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH Transform (ItemNumber, a, b) AS (
SELECT ItemNumber, MAX(a), MAX(b)
FROM (
SELECT ItemNumber, a=a.Item, b=NULL
FROM dbo.DelimitedSplit8K(@ff, @delim) a
UNION ALL
SELECT ItemNumber, NULL, Item
FROM dbo.DelimitedSplit8K(@rr, @delim)) a
GROUP BY ItemNumber),
rCTEReplace (n, s, r) AS (
SELECT n=1, @in, REPLACE(@in COLLATE Latin1_General_BIN, a, b)
FROM Transform
WHERE ItemNumber = 1
UNION ALL
SELECT n+1, s, REPLACE(r COLLATE Latin1_General_BIN, a, b)
FROM rCTEReplace
JOIN Transform ON ItemNumber = n+1
)
SELECT r
FROM rCTEReplace
WHERE n = (SELECT COUNT(*) FROM Transform)
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 23, 2013 at 3:25 am
Umm guyz.....This is my try at the requirement......Is this close enough??.....I used the tally CTE to create dynamic sql for the nested replace statements :
Declare @string Varchar(max), @sql Varchar(MAX)
Set @string = 'abcdefgh'
;With CTE
As
(
Select Top 100 ROW_NUMBER() Over(Order By (Select NULL)) As N From sys.columns
),
CTE1
As
(
Select Right(LEFT(@string, N), 1) aS Value, N
From CTE As b where N <= LEN(@string)
)
Select @sql = STUFF((Select '),' + CHAR(39) + Value + CHAR(39) + ',' + Cast(N As Varchar)
From CTE1 For XML PATH('')),1,1,'')
Select @sql = 'Select ' + REPLICATE('Replace(',LEN(@string)) + CHAR(39) + @string + CHAR(39) + @sql + ')'
Print @sql
Execute (@sql)
I am sorry if I am misinterpreting the requirement. Is this close to what the OP is looking for Dwain??
January 23, 2013 at 3:32 am
vinu512 (1/23/2013)
Umm guyz.....This is my try at the requirement......Is this close enough??.....I used the tally CTE to create dynamic sql for the nested replace statements :
Declare @string Varchar(max), @sql Varchar(MAX)
Set @string = 'abcdefgh'
;With CTE
As
(
Select Top 100 ROW_NUMBER() Over(Order By (Select NULL)) As N From sys.columns
),
CTE1
As
(
Select Right(LEFT(@string, N), 1) aS Value, N
From CTE As b where N <= LEN(@string)
)
Select @sql = STUFF((Select '),' + CHAR(39) + Value + CHAR(39) + ',' + Cast(N As Varchar)
From CTE1 For XML PATH('')),1,1,'')
Select @sql = 'Select ' + REPLICATE('Replace(',LEN(@string)) + CHAR(39) + @string + CHAR(39) + @sql + ')'
Print @sql
Execute (@sql)
I am sorry if I am misinterpreting the requirement. Is this close to what the OP is looking for Dwain??
Without testing it I can't be sure but there were a couple of prior dynamic SQL attack vectors explored (including one by me). My expectation is that they'll run faster than the TVFs being proposed, however as dynamic SQL they can't be put into a TVF (in an SP it's possible) so as a utility they're not quite as easy to use. Maybe - that last may be in the eye of the beholder.
The TVF (or iTVF) approach is the really fun challenge in my opinion!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 23, 2013 at 3:41 am
dwain.c (1/23/2013)
vinu512 (1/23/2013)
Umm guyz.....This is my try at the requirement......Is this close enough??.....I used the tally CTE to create dynamic sql for the nested replace statements :
Declare @string Varchar(max), @sql Varchar(MAX)
Set @string = 'abcdefgh'
;With CTE
As
(
Select Top 100 ROW_NUMBER() Over(Order By (Select NULL)) As N From sys.columns
),
CTE1
As
(
Select Right(LEFT(@string, N), 1) aS Value, N
From CTE As b where N <= LEN(@string)
)
Select @sql = STUFF((Select '),' + CHAR(39) + Value + CHAR(39) + ',' + Cast(N As Varchar)
From CTE1 For XML PATH('')),1,1,'')
Select @sql = 'Select ' + REPLICATE('Replace(',LEN(@string)) + CHAR(39) + @string + CHAR(39) + @sql + ')'
Print @sql
Execute (@sql)
I am sorry if I am misinterpreting the requirement. Is this close to what the OP is looking for Dwain??
Without testing it I can't be sure but there were a couple of prior dynamic SQL attack vectors explored (including one by me). My expectation is that they'll run faster than the TVFs being proposed, however as dynamic SQL they can't be put into a TVF (in an SP it's possible) so as a utility they're not quite as easy to use. Maybe - that last may be in the eye of the beholder.
The TVF (or iTVF) approach is the really fun challenge in my opinion!
You are right Dwain....as always. 🙂
Let me try the Recursive CTE approach...I'll get back....and it sure is fun.
January 23, 2013 at 4:40 am
vinu512 (1/23/2013)
You are right Dwain....as always. 🙂
Thank you sir, for the vote of confidence but in truth there's been enough times I've been wrong to keep my head out of the clouds and my feet firmly planted on terra firma. 😉
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 23, 2013 at 5:06 am
dwain.c (1/22/2013)
Gentlemen,Allow me to enter a new dog in this race! Perhaps you'll find the new approach amusing. I call it the Nested Replace by Quirky Update (NRxQU for short). In marketing speak, we could even say that its got a catchy name!...
Come on Chris - your turn to show us what yours can do!
Dwain, that's quite inspired. Who'd have thought of performing a QU in a function?
It can be speeded up though:
---------------------------------------------------------------------
Steven Willis (NestedReplaceSW)
SQL Server Execution Times:
CPU time = 1653 ms, elapsed time = 1741 ms.
---------------------------------------------------------------------
Dwain.C (NestedReplace)
SQL Server Execution Times:
CPU time = 1622 ms, elapsed time = 1614 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.
---------------------------------------------------------------------
ChrisM (rCTE - IF_MultipleReplace02)
SQL Server Execution Times:
CPU time = 1450 ms, elapsed time = 1468 ms.
---------------------------------------------------------------------
Dwain.C (NRxQU)
SQL Server Execution Times:
CPU time = 999 ms, elapsed time = 1111 ms.
---------------------------------------------------------------------
ChrisM (QU1)
SQL Server Execution Times:
CPU time = 827 ms, elapsed time = 868 ms.
---------------------------------------------------------------------
ChrisM (QU2)
SQL Server Execution Times:
CPU time = 608 ms, elapsed time = 631 ms.
---------------------------------------------------------------------
Here's that QU2 function definition:
ALTER FUNCTION [dbo].[NestedReplaceXQU_CM02]
(
-- Input parameters
@strTarget VARCHAR(8000),
@OldVal VARCHAR(8000),
@NewVal VARCHAR(8000),
@delim CHAR(1)
)
RETURNS @Results TABLE (OutputString VARCHAR(8000))
WITH SCHEMABINDING
AS
BEGIN
DECLARE @Mappingtable TABLE
(ItemNumber INT PRIMARY KEY CLUSTERED,
Item VARCHAR(8000))
INSERT INTO @Mappingtable (ItemNumber, Item)
SELECT a.ItemNumber, a.Item
FROM dbo.DelimitedSplit8K (@OldVal, @Delim) a
UPDATE m
SET @strTarget = REPLACE(@strTarget COLLATE LATIN1_GENERAL_BIN, m.Item, b.Item)
FROM @Mappingtable m
INNER JOIN dbo.DelimitedSplit8K (@NewVal,@Delim) b
ON b.ItemNumber = m.ItemNumber
INSERT INTO @Results SELECT @strTarget
RETURN
END
I was unaware that you could perform a QU against a table without updating any columns, but updating one or more of the running variables for each row. Good spot, geezer!
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
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply