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.
I tried STRING_SPLIT
function to split values and join the tables. I am struck with the logic to solve this problem.
1 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 |
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)
);
Col2
that exist inCol1
? Does the order of the remaining words inCol2
need to be preserved?