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.

Paul White
95.3k30 gold badges439 silver badges689 bronze badges
asked Apr 26, 2023 at 18:31
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? 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)
 );
answered Apr 26, 2023 at 20:15
0

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.