即使与另一个列值部分匹配，也将特定字符串替换为空白

样本数据

CREATE TABLE dbo.Test
(
    ID integer NOT NULL,
    Col1 varchar(8000) NOT NULL,
    Col2 varchar(8000) NOT NULL,
    Required_result varchar(8000) NULL
);

INSERT dbo.Test
    (ID, Col1, Col2)
VALUES
    (1, 'pencil red', 'charcoal, pencil, scale'),
    (2, 'pencil red', 'charcoal, red, scale'),
    (3, 'ball pen', 'eraser, pen'),
    (4, 'ball pen', 'ball, box'),
    (5, 'ball pen', 'eraser, ball pen'),
    (6, 'ball pen', 'eraser, pen ball'),
    (7, 'ball pen', 'eraser, watercan, bag'),
    (8, 'Note book', 'Note book'),
    (9, 'Note book', 'Note book, eraser'),
    (10, 'Note book', '');

解决方案

UPDATE dbo.Test
SET Required_result =
    ISNULL
    (
        (
            SELECT 
                -- Reassemble parts with no matches
                result = STRING_AGG(LTRIM(SS.[value]), ', ')
            FROM dbo.Test AS T2
            CROSS APPLY STRING_SPLIT
            (
                -- Remove commas before splitting
                REPLACE(T2.Col2, ',', ''),
                -- Split on spaces
                SPACE(1)
            ) AS SS
            WHERE
                -- For the current outer ID
                T2.ID = dbo.Test.ID
                -- Current part has no match
                AND 0 = 
                    CHARINDEX
                    (
                        SPACE(1) + LTRIM(SS.[value]) + SPACE(1), 
                        SPACE(1) + T2.Col1 + SPACE(1)
                    )
        ),
        -- Empty string instead of NULL if everything matched
        SPACE(0)
    );

结果

数据库<>小提琴

如果需要保证保留 Col2 字符串组件的顺序，请在（需要 Azure SQL 数据库或 SQL Server 2022）的可选enable_ordinal参数中传递值 1STRING_SPLIT并添加WITHIN GROUP (ORDER BY SS.ordinal)到STRING_AGG聚合中：

UPDATE dbo.Test
SET Required_result =
    ISNULL
    (
        (
            SELECT 
                -- Reassemble parts with no matches
                result = STRING_AGG(LTRIM(SS.[value]), ', ')
                    WITHIN GROUP (ORDER BY SS.ordinal)
            FROM dbo.Test AS T2
            CROSS APPLY STRING_SPLIT
            (
                -- Remove commas before splitting
                REPLACE(T2.Col2, ',', ''),
                -- Split on spaces
                SPACE(1),
                -- Enable ordinal
                1
            ) AS SS
            WHERE
                -- For the current outer ID
                T2.ID = dbo.Test.ID
                -- Current part has no match
                AND 0 = 
                    CHARINDEX
                    (
                        SPACE(1) + LTRIM(SS.[value]) + SPACE(1), 
                        SPACE(1) + T2.Col1 + SPACE(1)
                    )
        ),
        -- Empty string instead of NULL if everything matched
        SPACE(0)
    );