How to check if a number is in a comma separated list stored in a varchar column?

Question

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

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

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

 select * from myTable where 3 in (categoryIds) 

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

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

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

But this does not look like out of the box support for comma-separated lists, since it does not return anything.

Answer 1

You really have to redo this table to separate these comma separated values ​​into separate lines. However, if this is not possible, you do string comparisons 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 

Answer 2

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

Here @stringId is your search text. This way you can avoid unnecessary plurals when conditions

Regards, Raghu.M.

Answer 3

You can use dynamic SQL as follows:

 DECLARE @categoryIds nvarchar(50) = '1, 2, 3, 4, 5' EXEC ('SELECT * FROM myTable WHERE categoryId IN (' + @categoryIds + ')') 

Answer 4

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

 DECLARE @categoryId INT SET @categoryId = 3 SELECT * FROM myTable WHERE CHARINDEX(',' + CAST(@categoryId AS VARCHAR(MAX)) + ',', ',' + categoryIds + ',') > 0 

Answer 5

SELECT * FROM user_master WHERE (user_tags regexp '[[: <:]] 10 [[:>:]]' or user_tags regexp '[[: <:]] 11 [[:>:]]')

Answer 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");