如何在SQL Server中基于Lookup表更新含多字符串列的对应字段？

在SQL Server环境中，有一张待更新表（#targettest），其EN列包含多个以逗号分隔的字符串；另有一张Lookup表（#TargetFlatten1），存储EN与ID的映射关系。需要根据Lookup表的映射，将待更新表的Multicode列更新为对应ID的逗号拼接值。

表结构与数据 #

Lookup表（#TargetFlatten1） #

待更新表（#targettest） #

预期输出 #

当前代码 #

select EN, ID From #TargetFlatten1
select ResponseCode, EN, Multicode From #targettest

解决方案 #

方法1：适用于SQL Server 2016及以上版本 #

利用内置的STRING_SPLIT拆分逗号分隔字符串，STRING_AGG拼接对应ID：

WITH SplitAndMatch AS (
    SELECT 
        t.ResponseCode,
        t.EN AS OriginalEN,
        l.ID
    FROM #targettest t
    CROSS APPLY STRING_SPLIT(t.EN, ',') s
    JOIN #TargetFlatten1 l ON LTRIM(RTRIM(s.value)) = l.EN
)
UPDATE t
SET Multicode = (
    SELECT STRING_AGG(ID, ',') 
    FROM SplitAndMatch c
    WHERE c.ResponseCode = t.ResponseCode AND c.OriginalEN = t.EN
)
FROM #targettest t;

方法说明 #

1. STRING_SPLIT拆分EN列的逗号分隔值，通过LTRIM(RTRIM)处理值前后的空格（比如示例中Growth regulation, Leaf scald里的空格）；
2. 关联Lookup表匹配对应的ID；
3. STRING_AGG将同一行的ID重新拼接为逗号分隔字符串，更新到Multicode列。

方法2：适用于SQL Server 2016以下版本 #

自定义拆分函数，结合XML拼接实现需求：

-- 创建拆分字符串的自定义函数
CREATE FUNCTION dbo.SplitString (@String NVARCHAR(MAX), @Delimiter CHAR(1))
RETURNS @Result TABLE (Value NVARCHAR(MAX))
AS
BEGIN
    DECLARE @Xml XML = N'<root><r>' + REPLACE(@String, @Delimiter, '</r><r>') + '</r></root>'
    INSERT INTO @Result
    SELECT LTRIM(RTRIM(t.c.value('.', 'NVARCHAR(MAX)')))
    FROM @Xml.nodes('//root/r') t(c)
    RETURN
END
GO

-- 执行更新
UPDATE t
SET Multicode = STUFF((
    SELECT ',' + CAST(l.ID AS NVARCHAR(10))
    FROM dbo.SplitString(t.EN, ',') s
    JOIN #TargetFlatten1 l ON s.Value = l.EN
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM #targettest t;

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