SQL Server中如何比较字符串并找出ID列表的重叠部分？

嘿，这个需求我刚好碰到过，要找出两个逗号分隔ID列表的重叠部分，咱们可以通过用户定义函数（UDF）+ 字符串拼接的方式来实现，下面一步步给你拆解：

第一步：创建拆分逗号分隔字符串的UDF #

首先需要一个能把逗号分隔的字符串拆分成单条ID的函数，这样才能逐个对比两个列表里的ID。如果你的SQL Server版本是2016及以上，其实可以直接用内置的STRING_SPLIT函数，但为了兼容更早的版本，咱们写一个通用的UDF：

CREATE FUNCTION dbo.SplitString
(
    @InputString NVARCHAR(MAX),
    @Delimiter NVARCHAR(5)
)
RETURNS @OutputTable TABLE (Value NVARCHAR(100))
AS
BEGIN
    DECLARE @StartIndex INT, @EndIndex INT

    SET @StartIndex = 1
    IF SUBSTRING(@InputString, LEN(@InputString) - 1, LEN(@InputString)) <> @Delimiter
    BEGIN
        SET @InputString = @InputString + @Delimiter
    END

    WHILE CHARINDEX(@Delimiter, @InputString) > 0
    BEGIN
        SET @EndIndex = CHARINDEX(@Delimiter, @InputString)
        
        INSERT INTO @OutputTable(Value)
        SELECT LTRIM(RTRIM(SUBSTRING(@InputString, @StartIndex, @EndIndex - @StartIndex)))
        
        SET @InputString = SUBSTRING(@InputString, @EndIndex + 1, LEN(@InputString))
    END

    RETURN
END
GO

第二步：编写查询获取重叠ID #

接下来就可以用这个UDF拆分ID_SP和ID_GM字段，找出两个列表的交集，再把交集重新拼接成逗号分隔的字符串。这里分两种版本情况处理：

情况1：SQL Server 2017及以上（支持STRING_AGG） #

这个版本用STRING_AGG会更简洁直观：

SELECT 
    t.ID_SP,
    t.ID_GM,
    CASE WHEN COUNT(s.Value) > 0 THEN STRING_AGG(s.Value, ',') ELSE NULL END AS [overlap]
FROM 
    YourTableName t
CROSS APPLY 
    dbo.SplitString(t.ID_SP, ',') s
INNER JOIN 
    dbo.SplitString(t.ID_GM, ',') g ON s.Value = g.Value
GROUP BY 
    t.ID_SP, t.ID_GM

情况2：SQL Server 2016及以下（用FOR XML PATH拼接） #

如果你的版本不支持STRING_AGG，就用传统的FOR XML PATH方式实现拼接：

SELECT 
    DISTINCT
    t.ID_SP,
    t.ID_GM,
    STUFF(
        (SELECT ',' + s.Value
         FROM dbo.SplitString(t.ID_SP, ',') s
         INNER JOIN dbo.SplitString(t.ID_GM, ',') g ON s.Value = g.Value
         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
        1, 1, ''
    ) AS [overlap]
FROM 
    YourTableName t

验证示例数据 #

把你的示例数据代入测试：
假设表名为SalesGMData，数据如下：

执行上面的查询后，会得到你想要的结果：

小提示 #

• 如果你的SQL Server版本是2016+，可以直接把UDF替换成内置的STRING_SPLIT，性能会更好，比如把dbo.SplitString(t.ID_SP, ',')换成STRING_SPLIT(t.ID_SP, ',')。
• 函数里已经加了LTRIM(RTRIM)处理，能兼容ID前后带空格的情况，不用额外处理。

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