无需递归、循环、动态SQL/UFN，如何实现SQL批量REPLACE替换？

技术问询 #

是否可以在不使用递归、循环、动态SQL、用户定义函数（UFN）的情况下，实现REPLACE函数的批量替换效果？

更新说明 #

已重新梳理整个问题。

需求描述 #

希望通过一种高效的、基于集合（Set-based）的操作，一次性移除字符串中所有search_expression指定的单词。当前使用递归CTE的方法在小数据量下可正常运行，但处理500K行数据时性能将大幅下降，测试代码如下：

IF OBJECT_ID('TempDB..##TempStringsToClean') IS NOT NULL
    DROP TABLE ##TempStringsToClean;
GO

CREATE TABLE ##TempStringsToClean (
     [ID]                   INT
    ,[TheString]            VARCHAR(128)
    ,[ExprNum]              INT
    ,[SearchExpression]     VARCHAR(64)
);

INSERT INTO ##TempStringsToClean
SELECT *
FROM (VALUES
     (1, 'The Quick Brown Fox Jumps Over the Lazy Dog', 0, 'Brown'  )
    ,(1, 'The Quick Brown Fox Jumps Over the Lazy Dog', 1, 'Jump'   )
    ,(1, 'The Quick Brown Fox Jumps Over the Lazy Dog', 2, 'Over'   )
    ,(2, 'The Quick Brown Fox Jumps Over the Lazy Dog', 0, 'Quick'  )
    ,(2, 'The Quick Brown Fox Jumps Over the Lazy Dog', 1, 'F'      )
    ,(2, 'The Quick Brown Fox Jumps Over the Lazy Dog', 2, 'Lazy'   )
    ,(3, 'Order of operations is important for the Business Unit "BU".', 0, 'BU')
    ,(3, 'Order of operations is important for the Business Unit "BU".', 1, 'Business Unit')
    ,(4, 'Order of operations is important for the Business Unit "BU".', 0, 'Business Unit')
    ,(4, 'Order of operations is important for the Business Unit "BU".', 1, 'BU')
) AS VALUE([ID],[TheString],[ExprNum],[SearchExpression])
;

;WITH RecurCTE AS (
    SELECT
         TS.[ID]                
        ,TS.[TheString]     
        ,TS.[ExprNum]           
        ,TS.[SearchExpression]
        ,[NewString]            = REPLACE(TS.[TheString], TS.[SearchExpression], '')
        ,[Level]                = 1
    FROM ##TempStringsToClean TS
    WHERE [ExprNum] = 0
    
    UNION ALL

    SELECT
         TS.[ID]                
        ,TS.[TheString]     
        ,TS.[ExprNum]           
        ,TS.[SearchExpression]
        ,[NewString]            = REPLACE(R.[NewString], TS.[SearchExpression], '')
        ,[Level]                = R.[Level] + 1
    FROM ##TempStringsToClean TS
    INNER JOIN RecurCTE R ON TS.[ID] = R.[ID]
                            AND TS.[ExprNum] = R.[ExprNum]+1
    WHERE TS.[ExprNum] > 0
)


SELECT
     [ID]
    ,[TheString]
    ,[Replaced]     = STRING_AGG([SearchExpression], ', ') WITHIN GROUP (ORDER BY [ExprNum])
    ,[NewString]    = MAX([NewString])
    ,[Level]        = MAX([Level])
FROM RecurCTE R
GROUP BY
     [ID]
    ,[TheString]
ORDER BY [ID]
    ,[Level]
;

该代码可生成预期结果。

场景补充 #

此需求用于字符串相似度计算场景，需移除完整单词；且需优先替换较长的单词（尤其是包含短单词的长单词），例如先替换cleaning再替换lean。

已尝试的方案及不足 #

方法一：INTERSECT/EXCEPT实现 #

/*
    intersect/except try
    Works but I don't appreciate the new string not being agged back into it's original position.
    Not a requirement, but it helps when validating.
*/
;WITH SplitOriginalStringCTE AS (
    SELECT
         TC.[ID]
        ,TC.[TheString]
        ,[Word]             = ORIGSTRING.[value]
    FROM ##TempStringsToClean TC
    CROSS APPLY string_split([TheString], ' ') ORIGSTRING
    WHERE TC.[ExprNum] = 0
)

,SearchExpressionsCTE AS (
    SELECT
         TC.[ID]
        ,TC.[TheString]
        ,[Word]             = TC.[SearchExpression]
    FROM ##TempStringsToClean TC
)


SELECT
     [ID]
    ,[TheString]
    ,[NewString]        = STRING_AGG([Word], ' ') --WITHIN GROUP (ORDER BY [Rn])
FROM (
    SELECT *
    FROM SplitOriginalStringCTE O
    
    EXCEPT
    
    SELECT *
    FROM SearchExpressionsCTE N
) X
GROUP BY
     [ID]
    ,[TheString]
ORDER BY
     [ID]
    ,[TheString]

此方法可行，但新字符串无法保持原单词顺序，不利于验证。

方法二：CROSS APPLY实现 #

/*
    Use cross apply.
*/
;WITH SplitOriginalStringCTE AS (
    SELECT
         TC.[ID]
        ,TC.[TheString]
        ,[Word]             = ORIGSTRING.[value]
        --SQL Server 2019, SSMS shows the optional [ordinal] column as an intellisense column, but doesn't exist in 2019, docs say only in Azure.
        --Might be included in SQL Server 2022.  Relying on chance to hope they're in the right order.
        ,[Rn]           = ROW_NUMBER() OVER (ORDER BY [ID])
    FROM ##TempStringsToClean TC
    CROSS APPLY string_split([TheString], ' ') ORIGSTRING
    WHERE TC.[ExprNum] = 0
)


SELECT
     [ID]
    ,[TheString]
    ,[NewString]        = STRING_AGG([Word], ' ') WITHIN GROUP (ORDER BY [Rn])
FROM SplitOriginalStringCTE OS
WHERE NOT EXISTS (
            SELECT 1
            FROM ##TempStringsToClean EX
            WHERE OS.[ID] = EX.[ID]
                AND OS.[Word] = EX.[SearchExpression]
    )
GROUP BY
     [ID]
    ,[TheString]
ORDER BY
     [ID]
;

此方法可行，但依赖STRING_SPLIT的返回顺序（SQL Server 2019无ordinal列），无法保证顺序的准确性。

优化方案：基于集合式的高效批量替换 #

核心思路 #

1. 对每个ID的搜索表达式按长度降序排序，确保长单词优先被替换，避免短单词误匹配长单词的部分内容
2. 使用数字表拆分原字符串，严格保留每个单词的原始顺序
3. 过滤掉属于搜索表达式的单词，再按原始顺序拼接回字符串

实现代码 #

-- 创建临时数字表（用于拆分字符串，可复用）
IF OBJECT_ID('TempDB..#Numbers') IS NOT NULL
    DROP TABLE #Numbers;
GO

CREATE TABLE #Numbers (Num INT PRIMARY KEY);
INSERT INTO #Numbers (Num)
SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2;

-- 主逻辑处理
WITH OriginalStrings AS (
    -- 提取每个ID的唯一原始字符串，避免重复处理
    SELECT DISTINCT ID, TheString
    FROM ##TempStringsToClean
),
SearchExprs AS (
    -- 整理每个ID的搜索表达式，按长度降序排序
    SELECT 
        ID, 
        SearchExpression,
        LEN(SearchExpression) AS ExprLen
    FROM ##TempStringsToClean
    GROUP BY ID, SearchExpression
),
SplitStrings AS (
    -- 拆分原始字符串并保留单词的原始顺序
    SELECT 
        os.ID,
        os.TheString,
        -- 提取单个单词
        SUBSTRING(os.TheString, n.Num, CHARINDEX(' ', os.TheString + ' ', n.Num) - n.Num) AS Word,
        -- 标记单词的原始位置顺序
        ROW_NUMBER() OVER (PARTITION BY os.ID ORDER BY n.Num) AS WordOrder
    FROM OriginalStrings os
    JOIN #Numbers n 
        ON n.Num <= LEN(os.TheString)
        AND (n.Num = 1 OR SUBSTRING(os.TheString, n.Num - 1, 1) = ' ')
)
SELECT 
    ss.ID,
    ss.TheString,
    -- 按原始顺序拼接过滤后的单词
    STRING_AGG(ss.Word, ' ') WITHIN GROUP (ORDER BY ss.WordOrder) AS NewString,
    -- 拼接被替换的单词（按长度降序展示）
    STRING_AGG(se.SearchExpression, ', ') WITHIN GROUP (ORDER BY se.ExprLen DESC) AS Replaced
FROM SplitStrings ss
LEFT JOIN SearchExprs se 
    ON ss.ID = se.ID 
    AND ss.Word = se.SearchExpression
WHERE se.SearchExpression IS NULL -- 过滤掉需要替换的单词
GROUP BY ss.ID, ss.TheString
ORDER BY ss.ID;

方案优势 #

• 完全基于集合式操作，无递归、循环、动态SQL和UFN，性能更优，适合500K行级别的大数据量场景
• 通过数字表拆分字符串，严格保留单词的原始顺序，便于结果验证
• 搜索表达式按长度降序处理，确保长单词优先被替换，符合业务场景要求
• 避免了STRING_SPLIT在SQL Server 2019中无ordinal列的顺序问题

内容的提问来源于stack exchange，提问作者user2943131