Recursive replace from a table of characters
recursion, sql-server-2005, t-sql
Solution
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)
Problem
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.