SQL – Finding tokens in comma-delimited strings

Assume that you have a table with a column that contains comma-delimited strings – for example, like this:

CREATE TABLE #tmpA(strId VARCHAR(32));
INSERT INTO #tmpA
	SELECT '1,2,3' UNION ALL
	SELECT '1,3,4' UNION ALL
	SELECT '2,3,4' UNION ALL
	SELECT '3,4,5';

You are probably saying “Bleh! That should not happen!! It violates the very basics of a good database design”.  And, you would be absolutely right – but unfortunately, while you and I are on the same page, the guy or girl who designed the original database might have been on a different planet. And for better or for worse, you are forced to adopt that problem child.

In any case, if you want to find all the rows in the table that have a specific item in the comma-delimited list, you can use the LIKE operator. For example, to find all the rows that have a “2″ in the string, I could write a select statement like this:

DECLARE @id INT; SET @id = 2;
SELECT   strId
FROM     #tmpA
WHERE    ','+strId+',' LIKE '%,'+CAST(@id AS VARCHAR(32))+',%';

On the left side of the LIKE operator, adding those commas to the beginning and end of strId column ensures that every token has a comma before and after it. On the right side, we again add commas to the beginning and end of the value we want to search for.

You can extend this to search against column values in another table. As an example:

-- CREATE A TEST TABLE WITH VALUES TO LOOK FOR
CREATE TABLE #tmpB( id INT );
INSERT INTO #tmpB
	SELECT 1 UNION ALL
	SELECT 2;

-- LOOK FOR ROWS IN TABLE A WHICH HAVE OCCURRENCES OF
-- VALUES IN TABLE A
SELECT
	a.strId,
	b.Id
FROM
	#tmpA a
	INNER JOIN #tmpB b ON
		','+a.strId+',' LIKE '%,'+CAST(b.Id AS VARCHAR(32)) + ',%'

Another way of doing this would be to split the comma-separated string into a virtual table and then join with that table. But, call me crazy, I find this approach using the LIKE keyword much simper and easier to use!!