I have a table with Clients and their labels (saved in a single column separated by comma).
I need to find a way to EXCLUDE clients with SDRC label.
CREATE TABLE FakeData
(ClientId INT, ClientLabels VARCHAR(MAX));
INSERT INTO FakeData
VALUES
(1, 'ABA, SDRC, California'),
(2, 'SLP, Texas, SDRC (0-3)'),
(3, 'ABA, SDRC (0-3), SDRC, Virginia'),
(4, 'OT'),
(5, 'ABA, SDRC (6+)'),
(6, 'SLP, SDRC, Texas, SDRC (12+)')
SELECT * FROM FakeData
| ClientId | ClientLabels |
|---|---|
| 1 | ABA, SDRC, California |
| 2 | SLP, Texas, SDRC (0-3) |
| 3 | ABA, SDRC (0-3), SDRC, Virginia |
| 4 | OT |
| 5 | ABA, SDRC (6+) |
| 6 | SLP, SDRC, Texas, SDRC (12+) |
The output I need:
| ClientId | ClientLabels |
|---|---|
| 2 | SLP, Texas, SDRC (0-3) |
| 4 | OT |
| 5 | ABA, SDRC (6+) |
I was thinking to use STRING_SPLIT, however it did not work.
SELECT *
FROM FakeData
WHERE 'SDRC' NOT IN (SELECT VALUE FROM STRING_SPLIT(FakeData.ClientLabels, ','))
And obviously below code will not work as well.
SELECT *
FROM FakeData
WHERE FakeData.ClientLabels NOT LIKE '%SDRC%'
>Solution :
How about this:
SELECT *
FROM FakeData
WHERE 'SDRC' NOT IN (SELECT TRIM(VALUE) FROM STRING_SPLIT(FakeData.ClientLabels, ','))
http://sqlfiddle.com/#!18/717fc/3