Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I check whether a number is contained in comma separated list stored in a varchar column?

Tags:

sql

sql-server

I have a table with a varchar column categoryIds. It contains some IDs separated by commas, for example:

id       categoryIds
-------------------- 
1        3,7,12,33,43

I want to do a select statement and check if an int exists in that column. Something like this:

select * 
from myTable 
where 3 in (categoryIds)

I know this is possible in MySQL by doing this, but can it be done in SQL Server as well?

I have tried casting the int to a char, which runs the following statement:

select * 
from myTable 
where '3' in (categoryIds)

But it doesn't look like there's any "out of the box" support for comma separated lists as it returns nothing.

like image 824
Schileru Avatar asked Oct 22 '15 10:10

Schileru


2 Answers

You should really redesign this table to split out those values from being comma separated to being in individual rows. However, if this is not possible, you are stuck with doing string comparison instead:

DECLARE @id INT = 3
DECLARE @stringId VARCHAR(50) = CAST(@id AS VARCHAR(50))

SELECT * 
FROM MyTable 
WHERE categoryIds = @stringId -- When there is only 1 id in the table
OR categoryIds LIKE @stringId + ',%' -- When the id is the first one
OR categoryIds LIKE '%,' + @stringId + ',%' -- When the id is in the middle
OR categoryIds LIKE '%,' + @stringId -- When the id is at the end
like image 103
DavidG Avatar answered Oct 25 '22 00:10

DavidG


SELECT * 
FROM myTable 
WHERE (',' + RTRIM(categoryIds) + ',') LIKE '%,' + @stringId + ',%'

Here @stringId is your text to be searched. In this way you can avoid unnecessary multiple where conditions

Kind Regards, Raghu.M.

like image 45
Raghurocks Avatar answered Oct 24 '22 23:10

Raghurocks