SQL Server 2014：连接操作时实现单行多次更新及分隔符替换

问题场景 #

现有两张表及测试数据：

CREATE TABLE Replacements 
(
    OldVal nvarchar(max),
    NewVal nvarchar(max)
);

CREATE TABLE Foo
(
    Val nvarchar(max)
);

insert into Replacements values ('old1','new1');
insert into Replacements values ('old2','new2');
insert into Replacements values ('old3','new3');
insert into Replacements values ('old4','new4');

insert into Foo values ('old1');
insert into Foo values ('old3');
insert into Foo values ('old2;old4');

需将Foo表中（含分号分隔的多值）的旧值关联Replacements表替换为新值，但现有更新语句仅能完成首次替换：

update f
set f.val = Replace(f.val, r.OldVal, r.NewVal)
from Foo f 
inner join replacements r on (CHARINDEX(r.OldVal, f.Val) > 0);

执行后结果：

以下提供两种兼容SQL Server 2014的解决方案：

方法一：递归CTE批量替换所有匹配项 #

通过递归CTE遍历所有替换规则，对每行Foo数据依次执行替换，确保所有匹配的旧值都被处理：

WITH RecursiveReplace AS (
    -- 初始数据集：原始Foo值，替换规则起始索引
    SELECT 
        f.Val AS OriginalVal,
        f.Val AS CurrentVal,
        1 AS RuleIndex
    FROM Foo f

    UNION ALL

    -- 递归应用每个替换规则
    SELECT 
        rr.OriginalVal,
        REPLACE(rr.CurrentVal, r.OldVal, r.NewVal) AS CurrentVal,
        rr.RuleIndex + 1 AS RuleIndex
    FROM RecursiveReplace rr
    CROSS JOIN (
        -- 为替换规则添加索引，保证顺序执行
        SELECT *, 
               ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RuleNum
        FROM Replacements
    ) r
    WHERE rr.RuleIndex = r.RuleNum
)
-- 更新Foo表，取每个原始值的最终替换结果
UPDATE f
SET f.Val = rr.FinalVal
FROM Foo f
JOIN (
    SELECT OriginalVal, MAX(CurrentVal) AS FinalVal
    FROM RecursiveReplace
    GROUP BY OriginalVal
) rr ON f.Val = rr.OriginalVal;

SELECT * FROM Foo;

执行后正确结果：

方法二：拆分分隔值后替换再合并 #

针对分号分隔的多值，先拆分每个独立值，替换后重新拼接。由于SQL Server 2014无内置拆分/聚合函数，需自定义拆分函数：

1. 创建字符串拆分函数 #

CREATE FUNCTION dbo.SplitString
(
    @String NVARCHAR(MAX),
    @Delimiter CHAR(1)
)
RETURNS @Result TABLE (Value NVARCHAR(MAX))
AS
BEGIN
    DECLARE @StartIndex INT = 1, @EndIndex INT;

    -- 确保字符串以分隔符结尾，避免遗漏最后一个值
    IF RIGHT(@String, 1) <> @Delimiter
        SET @String += @Delimiter;

    WHILE CHARINDEX(@Delimiter, @String) > 0
    BEGIN
        SET @EndIndex = CHARINDEX(@Delimiter, @String);
        INSERT INTO @Result(Value)
        SELECT SUBSTRING(@String, @StartIndex, @EndIndex - @StartIndex);
        SET @String = SUBSTRING(@String, @EndIndex + 1, LEN(@String));
    END

    RETURN
END

2. 执行拆分、替换、合并更新 #

UPDATE f
SET f.Val = (
    SELECT STUFF((
        SELECT ';' + ISNULL(r.NewVal, s.Value)
        FROM dbo.SplitString(f.Val, ';') s
        LEFT JOIN Replacements r ON s.Value = r.OldVal
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
)
FROM Foo f;

SELECT * FROM Foo;

此方法精准处理每个分隔值，避免替换规则间的干扰（如旧值存在包含关系的场景）。

方案选择建议 #

• 递归替换法：无需额外函数，实现简单，适合旧值无嵌套包含的场景。
• 拆分合并法：处理更精准，适合旧值可能存在包含关系或需要单独校验每个值的场景。

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