In short, I am looking for a single recursive query that can perform multiple replaces over one string. I have a notion it can be done, but am failing to wrap my head around it.

Granted, I'd prefer the biz-layer of the application, or even the CLR, to do the replacing, but these are not options in this case.

More specifically, I want to replace the below mess - which is C&P in 8 different stored procedures - with a 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

Here is where I've 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

Notice I've joined all the offending characters into a single string separated by '-' (knowing that '-' will never be one of the offending characters), which is then split. The result from this query looks like:

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 replace clearly works, but now I want it to be recursive so I can pull the top 1 and eventually come out with:

TGT
------------
A-B-C-D-C-B-A

Any ideas on how to accomplish this with one query?

EDIT: Well, actual recursion isn't necessary if there's another way. I'm pondering the use of a table of numbers here, too.

• tsql
• sql-server-2005
• recursion

You can use this in a scalar function. I use it to remove all control characters from some external input.

SELECT @target = REPLACE(@target, invalidChar, '-')
FROM (VALUES ('~'),(''''),('!'),('@'),('#')) AS T(invalidChar)

I figured it out. I failed to mention that the tvf_SplitString function returns a row number as "pos" (although a subquery assigning row_number could also have worked). With that fact, I could control cross join between the recursive call and the split.

-- 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

Still, I'm open to other suggestions.