By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DROP TABLE IF EXISTS #ValidCode;
CREATE TABLE #ValidCode
(
ID INT IDENTITY(1,1)
, Code CHAR(1)
);
INSERT INTO #ValidCode (Code) VALUES ('A'), ('B'), ('C');
DROP TABLE IF EXISTS #SourceData;
CREATE TABLE #SourceData
(
ID INT IDENTITY(1,1)
, Codes VARCHAR(500)
, Is_Valid BIT
, Is_Split BIT
);
INSERT INTO #SourceData (Codes)
VALUES ('A;B;C')
, ('B;A')
, ('B;B;B;C;C;A;A;B')
, ('B;Z;1')
, ('B;ss;asd');
SELECT * FROM #ValidCode;
SELECT * FROM #SourceData;
WITH _BadData AS
(
SELECT DISTINCT SD.ID
FROM #SourceData AS SD
CROSS APPLY STRING_SPLIT(SD.Codes, ';') AS SS
WHERE NOT EXISTS
(
SELECT 1 AS RowExists
| ID | Code |
|---|---|
| 1 | A |
| 2 | B |
| 3 | C |
| ID | Codes | Is_Valid | Is_Split |
|---|---|---|---|
| 1 | A;B;C | null | null |
| 2 | B;A | null | null |
| 3 | B;B;B;C;C;A;A;B | null | null |
| 4 | B;Z;1 | null | null |
| 5 | B;ss;asd | null | null |
| ID | Codes | Is_Valid | Is_Split |
|---|---|---|---|
| 1 | A;B;C | True | null |
| 2 | B;A | True | null |
| 3 | B;B;B;C;C;A;A;B | True | null |
| 4 | B;Z;1 | False | null |
| 5 | B;ss;asd | False | null |