将2列与SQL Server 2012中的CSV值进行比较

-- 创建一个自定义函数，将CSV值拆分为多个行
CREATE FUNCTION dbo.SplitString
(
    @String VARCHAR(MAX),
    @Delimiter CHAR(1)
)
RETURNS @Result TABLE (Value VARCHAR(MAX))
AS
BEGIN
    DECLARE @StartIndex INT, @EndIndex INT
    SET @StartIndex = 1
    SET @EndIndex = CHARINDEX(@Delimiter, @String)

    WHILE @EndIndex > 0
    BEGIN
        INSERT INTO @Result (Value)
        SELECT SUBSTRING(@String, @StartIndex, @EndIndex - @StartIndex)

        SET @StartIndex = @EndIndex + 1
        SET @EndIndex = CHARINDEX(@Delimiter, @String, @StartIndex)
    END

    INSERT INTO @Result (Value)
    SELECT SUBSTRING(@String, @StartIndex, LEN(@String) - @StartIndex + 1)

    RETURN
END

-- 使用自定义函数将CSV值拆分为多个行，并与另一列进行比较
SELECT t1.Column1, t2.Value
FROM Table1 t1
CROSS APPLY dbo.SplitString(t1.Column2, ',') t2
WHERE t1.Column1 = t2.Value

-- 使用LIKE运算符将CSV值与另一列进行比较
SELECT Column1, Column2
FROM Table1
WHERE ',' + Column2 + ',' LIKE '%,' + Column1 + ',%'