18

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.

Community
  • 1
  • 1
Schileru
  • 325
  • 2
  • 4
  • 11
  • 5
    You should think about your db design. Storing values in csv format is not a good practice. – Jens Oct 22 '15 at 10:27
  • 3
    Don't store data as comma separated values in the solution. One value per row is the SQL way! – jarlh Oct 22 '15 at 10:27
  • 2
    Possibly something like this: `SELECT * FROM myTable WHERE ',' + categoryId + ',' LIKE '%,3,%';` – dwjv Oct 22 '15 at 10:38
  • Thanks for the input, we ended up doing it the "SQL way" instead :) – Schileru Oct 23 '15 at 13:35
  • Good on you for normalizing the table! Storing csv lists makes *everything* harder ... as evidenced by this question. – SOS Feb 19 '22 at 05:09

7 Answers7

20

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
DavidG
  • 113,891
  • 12
  • 217
  • 223
9
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.

Raghurocks
  • 917
  • 7
  • 17
6

Because it has not been mentioned yet, one might use STRING_SPLIT([values], ',') to achieve the desired check. The function is available since SQL Server 2016. Due the age of the question I assume that this perquisite wasn't met, the time it has been asked.

select [id], [categoryIds] 
from [myTable] 
where '3' in (select value from STRING_SPLIT([categoryIds], ','))

This should outperform the above mentioned string based comparisons.

ana
  • 1,071
  • 9
  • 12
Dominik Klug
  • 139
  • 1
  • 5
2

Not sure if this would be faster or slower than DavidG's suggestion, but in order to get the same matches with only one check, you can do:

DECLARE @categoryId INT
SET @categoryId = 3

SELECT *
FROM myTable
WHERE CHARINDEX(',' + CAST(@categoryId AS VARCHAR(MAX)) + ',', ',' + categoryIds + ',') > 0
MikeBeaton
  • 3,314
  • 4
  • 36
  • 45
0

You could use dynamic SQL like this:

DECLARE     @categoryIds    nvarchar(50) = '1, 2, 3, 4, 5'

EXEC        ('SELECT      *
              FROM        myTable
              WHERE       categoryId IN (' + @categoryIds + ')')
Peter Elzinga
  • 406
  • 3
  • 12
0
SELECT *
FROM user_master
WHERE user_tags REGEXP '[[:<:]]10[[:>:]]'
   OR user_tags REGEXP '[[:<:]]11[[:>:]]'
schlebe
  • 3,387
  • 5
  • 37
  • 50
Chirag Pipariya
  • 445
  • 5
  • 13
-6

use FIND_IN_SET() mysql function

Syntax

SELECT * FROM <table name> as a WHERE FIND_IN_SET(value to search in string,comma separated string);

Example

SELECT * FROM <table name> as a WHERE FIND_IN_SET(5,"1,2,3,4,5,6");
H. Pauwelyn
  • 13,575
  • 26
  • 81
  • 144
Sujal Patel
  • 592
  • 2
  • 5
  • 14