从字符表递归替换 [英] Recursive replace from a table of characters

问题描述

简而言之，我正在寻找可以对一个字符串执行多次替换的单个递归查询.我有一个想法是可以做到的，但我无法理解它.

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.

当然，我更喜欢应用程序的商业层，甚至是 CLR 来进行替换，但在这种情况下，这些不是选项.

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.

更具体地说，我想用 TVF 替换下面的混乱 - 这是 8 个不同存储过程中的 C&P.

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

这是我开始的地方:

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

所以，替换显然有效，但现在我希望它是递归的，这样我就可以拉取前 1 名并最终得出:

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?

好吧，如果有另一种方式，则不需要实际的递归.我也在考虑在这里使用数字表.

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

推荐答案

您可以在标量函数中使用它.我用它从一些外部输入中删除所有控制字符.

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)

这篇关于从字符表递归替换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！