Replace specific string with blank even if partial match with another column value

Question

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.

• J.D.: 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?

Answer 1

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