sql-server - 有效清理表格中的字符串

Question

我目前正在解决需要从表中存在的字符串中清除某些字符的问题。通常我会用替换来做一个简单的更新，但在这种情况下，需要删除 32 个不同的字符。

我已经环顾四周，但找不到任何很好的解决方案来快速清理表格中已经存在的字符串。

我调查过的事情:

进行一系列嵌套替换

这个解决方案是可行的，但对于 32 种不同的替换，它需要一些丑陋的代码或 hacky 动态 sql 来构建大量的替换。

PATINDEX 和 while 循环

如 this answer 中所见可以模拟一种正则表达式替换，但我正在处理大量数据，因此我什至不愿相信改进的解决方案在数据量很大时在合理的时间内运行。

递归 CTE

我尝试了一个 CTE approuch 来解决这个问题，但是一旦行数变大，它的运行速度并没有那么快。

以供引用:

CREATE TABLE #BadChar(
    id int IDENTITY(1,1),
    badString nvarchar(10),
    replaceString nvarchar(10)

);

INSERT INTO #BadChar(badString, replaceString) SELECT 'A', '^';
INSERT INTO #BadChar(badString, replaceString) SELECT 'B', '}';
INSERT INTO #BadChar(badString, replaceString) SELECT 's', '5';
INSERT INTO #BadChar(badString, replaceString) SELECT '-', ' ';

CREATE TABLE #CleanMe(
    clean_id int IDENTITY(1,1),
    DirtyString nvarchar(20)
);

DECLARE @i int;
SET @i = 0;
WHILE @i < 100000 BEGIN
    INSERT INTO #CleanMe(DirtyString) SELECT 'AAAAA';
    INSERT INTO #CleanMe(DirtyString) SELECT 'BBBBB';
    INSERT INTO #CleanMe(DirtyString) SELECT 'AB-String-BA';
    SET @i = @i + 1
END;


WITH FixedString (Step, String, cid) AS (
    SELECT 1 AS Step, REPLACE(DirtyString, badString, replaceString), clean_id
    FROM #BadChar, #CleanMe
    WHERE id = 1

    UNION ALL

    SELECT Step + 1, REPLACE(String, badString, replaceString), cid
    FROM FixedString AS T1
    JOIN #BadChar AS T2 ON T1.step + 1 = T2.id
    Join #CleanMe AS T3 on T1.cid = t3.clean_id

)
SELECT String FROM FixedString WHERE step = (SELECT MAX(STEP) FROM FixedString);

DROP TABLE #BadChar;
DROP TABLE #CleanMe;

使用 CLR

似乎这是许多人使用的常见解决方案，但我所处的环境并不使它成为一个很容易着手的解决方案。

我已经看过了，还有其他方法可以解决这个问题吗？或者我已经为此研究过的方法有什么改进？

Answer 1

利用来自 Alan Burstein's solution 的想法，如果你想硬编码坏的/替换字符串，你可以做这样的事情。这也适用于长于单个字符的坏字符串/替换字符串。

CREATE FUNCTION [dbo].[CleanStringV1]
(
  @String   nvarchar(4000)
)
RETURNS nvarchar(4000) WITH SCHEMABINDING AS 
BEGIN
 SELECT @string = REPLACE
  (
    @string COLLATE Latin1_General_BIN,
    badString,
    replaceString
  )
 FROM
 (VALUES
      ('A', '^')
    , ('B', '}')
    , ('s', '5')
    , ('-', ' ')
    ) t(badString, replaceString) 
 RETURN @string;
END;

或者，如果你有一个包含坏/替换字符串的表，那么

CREATE FUNCTION [dbo].[CleanStringV2]
(
  @String   nvarchar(4000)
)
RETURNS nvarchar(4000) AS 
BEGIN
 SELECT @string = REPLACE
  (
    @string COLLATE Latin1_General_BIN,
    badString,
    replaceString
  )
 FROM BadChar
 RETURN @string;
END;

这些是区分大小写的。如果您想要不区分大小写，您可以删除 COLLATE 位。我做了一些小测试，这些测试并不比嵌套 REPLACE 慢多少。第一个硬编码字符串是两者中更快的一个，几乎和嵌套的 REPLACE 一样快。