Azure SQL中用T-SQL批量替换大JSON字符串特定区间文本

针对Azure SQL数据库中存在未转义双引号的大JSON字符串（单单元格50000字符），需要批量替换所有"body":"与"subject":之间的内容，以下是两种可行方案：

方法1：递归CTE处理单条记录内的所有匹配并批量更新 #

递归CTE可逐次替换每条记录中的目标片段，直至无匹配项，最终将结果写回原表：

WITH RecursiveReplace AS (
    -- 初始查询：获取所有记录并标记是否存在待替换片段
    SELECT 
        Id, -- 替换为你的表主键字段，用于唯一标识记录
        JsonResponse,
        CASE 
            WHEN CHARINDEX('"body":"', JsonResponse) > 0 
                 AND CHARINDEX('"subject":', JsonResponse) > CHARINDEX('"body":"', JsonResponse) 
            THEN 1 
            ELSE 0 
        END AS HasMatch
    FROM stage.JsonResponse

    UNION ALL

    -- 递归替换：处理当前记录的第一个匹配片段，继续检查剩余匹配
    SELECT 
        Id,
        STUFF(JsonResponse, 
              CHARINDEX('"body":"', JsonResponse), 
              CHARINDEX('"subject":', JsonResponse) - CHARINDEX('"body":"', JsonResponse) + 1, 
              '"bodyX":"replaced text","'), -- 替换为你需要的内容，可修改字段名或值
        CASE 
            WHEN CHARINDEX('"body":"', 
                          STUFF(JsonResponse, 
                                CHARINDEX('"body":"', JsonResponse), 
                                CHARINDEX('"subject":', JsonResponse) - CHARINDEX('"body":"', JsonResponse) + 1, 
                                '"bodyX":"replaced text","')) > 0 
                 AND CHARINDEX('"subject":', 
                              STUFF(JsonResponse, 
                                    CHARINDEX('"body":"', JsonResponse), 
                                    CHARINDEX('"subject":', JsonResponse) - CHARINDEX('"body":"', JsonResponse) + 1, 
                                    '"bodyX":"replaced text","')) > CHARINDEX('"body":"', 
                                                                             STUFF(JsonResponse, 
                                                                                   CHARINDEX('"body":"', JsonResponse), 
                                                                                   CHARINDEX('"subject":', JsonResponse) - CHARINDEX('"body":"', JsonResponse) + 1, 
                                                                                   '"bodyX":"replaced text","'))
            THEN 1 
            ELSE 0 
        END AS HasMatch
    FROM RecursiveReplace
    WHERE HasMatch = 1
)
-- 更新原表：取每条记录最终替换完成的版本
UPDATE t
SET t.JsonResponse = r.JsonResponse
FROM stage.JsonResponse t
JOIN (
    SELECT Id, JsonResponse,
           ROW_NUMBER() OVER (PARTITION BY Id ORDER BY HasMatch ASC) AS rn
    FROM RecursiveReplace
) r ON t.Id = r.Id
WHERE r.rn = 1;

说明： #

• 必须使用表的主键（如Id）分组，避免不同记录的替换逻辑混淆
• 递归终止条件为HasMatch=0，即当前记录已无待替换片段
• 可将替换后的"bodyX"改回"body"，值用无转义风险的内容（如空字符串""）

方法2：自定义标量函数批量替换 #

若需重复执行此类操作，可创建函数处理单条字符串的所有匹配，再批量更新：

CREATE FUNCTION dbo.ReplaceAllBodySegments(@Input NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @PosStart INT, @PosEnd INT;
    SET @PosStart = CHARINDEX('"body":"', @Input);
    
    WHILE @PosStart > 0
    BEGIN
        SET @PosEnd = CHARINDEX('"subject":', @Input, @PosStart);
        IF @PosEnd = 0 BREAK; -- 找不到对应subject，终止循环
        
        -- 替换当前匹配片段
        SET @Input = STUFF(@Input, @PosStart, @PosEnd - @PosStart + 1, '"bodyX":"replaced text","');
        
        -- 定位下一个匹配的起始位置
        SET @PosStart = CHARINDEX('"body":"', @Input, @PosEnd - (@PosEnd - @PosStart) + LEN('"bodyX":"replaced text","'));
    END
    
    RETURN @Input;
END;

执行批量更新：

UPDATE stage.JsonResponse
SET JsonResponse = dbo.ReplaceAllBodySegments(JsonResponse);

注意事项： #

• 标量函数在处理超大规模数据（数百万行）时性能可能不及递归CTE，需根据数据量选择
• 测试阶段先执行SELECT dbo.ReplaceAllBodySegments(JsonResponse) FROM stage.JsonResponse验证结果，再执行更新
• 替换文本尽量简短，避免原字符串过度膨胀

关键优化提示： #

• 避免无限循环：递归CTE通过HasMatch标记终止，循环函数通过判断@PosEnd是否存在终止
• 确保匹配顺序：仅当"subject":出现在"body":"之后才执行替换，避免错误替换无关片段
• 适配大字符串：使用NVARCHAR(MAX)类型，支持50000字符的JSON内容

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