0

I have a scenario to replace entire string from a set of comma delimited strings, if partial word matches with another column.

I found very complex to explain in words and hence I have explained through a screenshot having various examples.

Please help me with stored procedure or a function or query to get results as in Required_result column as in below screen shot.

screenshot

I tried STRING_SPLIT function to split values and join the tables. I am struck with the logic to solve this problem.

1
  • So you want to remove any words in Col2 that exist in Col1? Does the order of the remaining words in Col2 need to be preserved?
    – J.D.
    Commented Apr 26, 2023 at 18:46

1 Answer 1

7

Sample data

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', '');

Solution

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)
    );

Result

ID Col1 Col2 Required_result
1 pencil red charcoal, pencil, scale charcoal, scale
2 pencil red charcoal, red, scale charcoal, scale
3 ball pen eraser, pen eraser
4 ball pen ball, box box
5 ball pen eraser, ball pen eraser
6 ball pen eraser, pen ball eraser
7 ball pen eraser, watercan, bag eraser, watercan, bag
8 Note book Note book
9 Note book Note book, eraser eraser
10 Note book

db<>fiddle


If you need to guarantee the ordering of Col2 string components is preserved, pass a value of 1 in the optional enable_ordinal parameter of STRING_SPLIT (Azure SQL Database or SQL Server 2022 required) and add WITHIN GROUP (ORDER BY SS.ordinal) to the STRING_AGG aggregate:

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)
    );
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.