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

CodePudding user response：

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

CodePudding user response：

use follwing query

SELECT ProspectID
FROM @temp
 WHERE TAGID LIKE '%'   @Filter   '%'
 or TagID  in (PARSENAME(REPLACE(@Filter,',','.'),2) )