SQL Server中按位置替换NVARCHAR(MAX)字符串内单词的x字符

需求分析 #

需要对NVARCHAR(MAX)列中的字符串按规则替换x字符：

• 单词开头的x（含大小写）→ 替换为AAA
• 单词中间的x（含大小写）→ 替换为MMM
• 单词结尾的x（含大小写）→ 替换为ZZZ
• 若单词仅由x组成（如xx），则开头的x替换为AAA，结尾的x替换为ZZZ，最终得到AAAZZZ

实现步骤 #

通过自定义标量函数处理单个单词 + 递归CTE拆分拼接字符串的方式实现，具体如下：

1. 创建处理单个单词的标量函数

该函数负责将单个单词中的x按位置规则替换，同时统一转为小写以匹配示例输出：

CREATE FUNCTION dbo.ReplaceXInWord(@Word NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Result NVARCHAR(MAX) = LOWER(@Word);

    -- 替换单词开头的x
    WHILE PATINDEX(N'^x', @Result) > 0
    BEGIN
        SET @Result = STUFF(@Result, 1, 1, N'AAA');
    END

    -- 替换单词结尾的x
    WHILE PATINDEX(N'x$', @Result) > 0
    BEGIN
        SET @Result = STUFF(@Result, LEN(@Result), 1, N'ZZZ');
    END

    -- 替换单词中间的x（前后为非x字符）
    WHILE PATINDEX(N'%[^x]x[^x]%', @Result) > 0
    BEGIN
        DECLARE @Pos INT = PATINDEX(N'%[^x]x[^x]%', @Result) + 1;
        SET @Result = STUFF(@Result, @Pos, 1, N'MMM');
    END

    RETURN @Result;
END

2. 拆分字符串并批量处理

使用递归CTE将原字符串拆分为单个单词，调用上述函数处理后，再用STRING_AGG拼接回完整字符串：

-- 假设目标表为TestTable，包含Id（主键）和Content（待处理的NVARCHAR(MAX)列）
WITH SplitWords AS (
    SELECT
        Id,
        CAST('' AS NVARCHAR(MAX)) AS Word,
        Content + ' ' AS Remaining,
        0 AS Level
    FROM TestTable
    UNION ALL
    SELECT
        Id,
        CAST(LEFT(Remaining, CHARINDEX(' ', Remaining) - 1) AS NVARCHAR(MAX)),
        CAST(SUBSTRING(Remaining, CHARINDEX(' ', Remaining) + 1, LEN(Remaining)) AS NVARCHAR(MAX)),
        Level + 1
    FROM SplitWords
    WHERE Remaining > ''
)
SELECT
    Id,
    STRING_AGG(dbo.ReplaceXInWord(Word), ' ') AS ProcessedContent
FROM SplitWords
WHERE Word > ''
GROUP BY Id
OPTION (MAXRECURSION 0);

3. 测试验证

将示例输入字符串代入测试：

• 输入：'This is text with far too much xx char xanthic Mix Excise xerox Ox Maximize'
• 输出：'This is teMMMt with far too much AAAZZZ char AAAanthic miZZZ eMMMcise AAAeroZZZ oZZZ maMMMimise'
  完全匹配预期结果。

注意事项 #

• 若不需要统一转为小写，可删除函数中LOWER(@Word)的转换，同时修改PATINDEX匹配大小写（如^[xX]）
• MAXRECURSION 0用于处理超长字符串，避免递归层级限制
• 若数据库版本不支持STRING_AGG（SQL Server 2017及以上支持），可改用FOR XML PATH拼接字符串

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