Sql split comma separated data and check with other table

Question

I have a one table with 2 fields one for tag and another for ProspectID

DECLARE @Filter NVARCHAR(251) ='30,40'

declare @temp table
(
    TagID       NVARCHAR(MAX),
    ProspectID  INT
)

INSERT INTO @temp(TAGID,ProspectID)
 
VALUES 

        ('20,30,40' ,1),
        ('30,50' ,2),
        ('20,30,40' ,3),
        ('60,70' ,4),
        ('30' ,5)

Need to return 30 contains prospectID and 40 contains as per my example

Output I need

ProspectID  
1
3
5

Does this answer your question? [Turning a Comma Separated string into individual rows](https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows) — Thom A, Mar 08 '22 at 10:37
Does this answer your question? [Passing a varchar full of comma delimited values to a SQL Server IN function](https://stackoverflow.com/a/67914697/2029983) — Thom A, Mar 08 '22 at 10:38
what is the differnce between 2 and 5 in ProspectID, why is 2 is not included in desired result and why is 5 is included in desired result? I think the 1,3 is enough. — RF1991, Mar 08 '22 at 14:18

score 0 · Answer 1 · answered Mar 08 '22 at 13:25

This is a question of Relational Division With Remainder, of which there are many solutions. I will present one common one.

You can use STRING_SPLIT to break up your values:

declare @temp table
(
    TagID       NVARCHAR(MAX),
    ProspectID  INT
)

INSERT INTO @temp(TAGID,ProspectID)
 
VALUES 

        ('20,30,40' ,1),
        ('30,50' ,2),
        ('20,30,40' ,3),
        ('60,70' ,4),
        ('30' ,5)

DECLARE @Filter NVARCHAR(251) ='30,40'

SELECT
  t.ProspectID
FROM @temp t
WHERE EXISTS (SELECT 1
    FROM STRING_SPLIT(@Filter, ',') f
    LEFT JOIN STRING_SPLIT(t.TagID, ',') t ON t.value = f.value
    HAVING COUNT(t.value) = COUNT(*)  -- none missing
);

db<>fiddle

However, your schema design is flawed. Do not store multiple pieces of information in one column or value. Instead store them in separate rows.

So you would have a table ProspectTag storing each combination (what you get by splitting the strings into separate rows), and @Filter should be a table variable or Table Valued Parameter also.

declare @temp table
(
    TagID       int,
    ProspectID  int
);

INSERT INTO @temp (TagID, ProspectID)
VALUES
(20, 1),
(30, 1),
(40, 1),
(30, 2),
(50, 2),
(20, 3),
(30, 3),
(40, 3),
(60, 4),
(70, 4),
(30, 5);

DECLARE @Filter TABLE(value int PRIMARY KEY);
INSERT @Filter (value) VALUES
(30),
(40);

DECLARE @totalFilters int = (SELECT COUNT(*) FROM @Filter);

SELECT
  t.ProspectID
FROM @temp t
JOIN @Filter f ON t.TagID = f.value
GROUP BY
  t.ProspectID
HAVING COUNT(*) = @totalFilters;  -- none missing

db<>fiddle

I know, but there seems no logic in why it was included, and not `2`, so guessing it was a mistake. If OP wants all possible matches then this could be easily modified to use a straight join. Let's wait and see what OP says — Charlieface, Mar 08 '22 at 15:10

RF1991 · Answer 2 · 2022-03-08T14:36:42.730

-1

use follwing query

SELECT ProspectID
FROM @temp
 WHERE TAGID LIKE '%' + @Filter + '%'
 --WHERE TAGID LIKE '%' + LTRIM(RTRIM(@Filter)) + '%'
 or TagID  in (PARSENAME(REPLACE(@Filter,',','.'),2) )--for ProspectID=5

edited Mar 08 '22 at 14:36

answered Mar 08 '22 at 12:04

RF1991

2,037
4
8
17

Sql split comma separated data and check with other table

2 Answers2