Filter rows based on comma-separated string-CodePudding

I have a set of ids as a comma separated string like '2461519,2461520,2461521'. I need to pull all the records from a table where the exact id is present in the comma separated string above.

here is how I am doing it

Select id from absence where charIndex(cast(id as varchar), '2461519,2461520,2461521') > 0

The expected result is

2461519
2461520
2461521

But, the query above also picked

How do I modify this to get the ones that are only exact matches.

CodePudding user response：

You can concatenate a comma to the beginning and end of the id list and the lookup value which forces CHARINDEX to match on an id encapsulated by commas, ensuring it won't return a partial match.

Select id
FROM absence
WHERE CHARINDEX(CONCAT(',', CAST(id AS VARCHAR(100)), ','), CONCAT(',','2461519,2461520,2461521', ',')) > 0;

Here's a working example:

DECLARE @ids VARCHAR(100) = '2461519,2461520,2461521';
DECLARE @t TABLE (id INT);
INSERT @t (id)
VALUES (2461519),(2461520),(2461521),(461519),(461520),
       (461521),(246151),(246152),(246152), (461), (519),
       (520), (24615);

Select id
FROM @t absence
WHERE CHARINDEX(CONCAT(',', CAST(id AS VARCHAR(100)), ','), CONCAT(',', @ids, ',')) > 0;

Here is a solution using string_split for SQL Server 2016 and newer:

DECLARE @ids VARCHAR(100) = '2461519,2461520,2461521';
DECLARE @t TABLE (id INT);
INSERT @t (id)
VALUES (2461519),(2461520),(2461521),(461519),(461520),
       (461521),(246151),(246152),(246152), (461), (519),
       (520), (24615);

SELECT absencse.id
FROM @t absencse
INNER JOIN STRING_SPLIT(@ids, ',') ss ON absencse.id = ss.value;