Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

How Can I Exclude Specific Records Correctly – SQL Server

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.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading