英文:
How Can I Exclude Specific Records Correctly - SQL Server
问题
ClientId | ClientLabels |
---|---|
2 | SLP, Texas, SDRC (0-3) |
4 | OT |
5 | ABA, SDRC (6+) |
英文:
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 of using 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%'
答案1
得分: 1
以下是您提供的代码的中文翻译:
如何看待这个:
SELECT *
FROM FakeData
WHERE 'SDRC' NOT IN (SELECT TRIM(VALUE) FROM STRING_SPLIT(FakeData.ClientLabels, ','))
http://sqlfiddle.com/#!18/717fc/3
英文:
How about this:
SELECT *
FROM FakeData
WHERE 'SDRC' NOT IN (SELECT TRIM(VALUE) FROM STRING_SPLIT(FakeData.ClientLabels, ','))
答案2
得分: 1
你可以将STRING_SPLIT
用作表格:
SELECT ClientId, ClientLabels
FROM @FakeData
CROSS APPLY STRING_SPLIT(ClientLabels, ',') s
GROUP BY ClientId, ClientLabels
HAVING MAX(CASE WHEN TRIM(value) = 'SDRC' THEN 1 ELSE 0 END) = 0
在进行比较后重新聚合。要更改筛选器,只需在Case语句中添加或删除值。
英文:
You can use STRING_SPLIT
as a table:
SELECT ClientId, ClientLabels
FROM @FakeData
CROSS APPLY STRING_SPLIT(ClientLabels, ',') s
GROUP BY ClientId, ClientLabels
HAVING MAX(CASE WHEN TRIM(value) = 'SDRC' THEN 1 ELSE 0 END) = 0
Re-aggregating it after making your comparisons. To alter the filter just add or remove values in the case statement.
答案3
得分: 1
以下是翻译好的部分:
如果您必须存储这样的数据,最简单的方法是使用类似匹配:
select *
from FakeData
where Concat(', ', ClientLabels, ', ') not like '%, SDRC, %';
英文:
The simplest way if you must store data like this, would be to use a like match
select *
from FakeData
where Concat(', ', ClientLabels, ', ') not like '%, SDRC, %';
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论