Recursive replacement from symbol table
In short, I'm looking for a single recursive query that can do multiple measurements on a single row. I have an idea that it can be done, but I cannot wrap my head around myself.
Of course, I would prefer that the application business class, or even the CLR do the replacement, but in this case, these are not parameters.
Specifically, I want to replace the bottom mess - which is C&P in 8 different stored procedures - with TVF.
SET @temp = REPLACE(RTRIM(@target), '~', '-')
SET @temp = REPLACE(@temp, '''', '-')
SET @temp = REPLACE(@temp, '!', '-')
SET @temp = REPLACE(@temp, '@', '-')
SET @temp = REPLACE(@temp, '#', '-')
-- 23 additional lines reducted
SET @target = @temp
This is where I started:
-- I have a split string TVF called tvf_SplitString that takes a string
-- and a splitter, and returns a table with one row for each element.
-- EDIT: tvf_SplitString returns a two-column table: pos, element, of which
-- pos is simply the row_number of the element.
SELECT REPLACE('A~B!C@D@C!B~A', MM.ELEMENT, '-') TGT
FROM dbo.tvf_SplitString('~-''-!-@-#', '-') MM
Note that I have concatenated all of the offending characters into one string, separated by "-" (knowing that "-" will never be one of the offending characters), which is then delimited. The result of this query looks like this:
TGT
------------
A-B!C@D@C!B-A
A~B!C@D@C!B~A
A~B-C@D@C-B~A
A~B!C-D-C!B~A
A~B!C@D@C!B~A
So the replacement obviously works, but now I want it to be recursive, so I can pull the top 1 and end up exiting:
TGT
------------
A-B-C-D-C-B-A
Any ideas on how to do this with a single request?
EDIT: Well, the actual recursion is unnecessary if there is another way. I am also considering using a number table.
source to share
I understood that. I didn't mention that the tvf_SplitString function returns the row number as "pos" (although a subquery assigning row_number might work as well). With this fact, I could control the cross join between the recursive call and the section.
-- the cast to varchar(max) matches the output of the TVF, otherwise error.
-- The iteration counter is joined to the row number value from the split string
-- function to ensure each iteration only replaces on one character.
WITH XX AS (SELECT CAST('A~B!C@D@C!B~A' AS VARCHAR(MAX)) TGT, 1 RN
UNION ALL
SELECT REPLACE(XX.TGT, MM.ELEMENT, '-'), RN + 1 RN
FROM XX, dbo.tvf_SplitString('~-''-!-@-#', '-') MM
WHERE XX.RN = MM.pos)
SELECT TOP 1 XX.TGT
FROM XX
ORDER BY RN DESC
However, I am open to other suggestions.
source to share