mysql check if numbers are in a comma separated list

Question

Hi

I have a table like this:

UID(int) NUMBERS(blob)
----------------------
1        1,13,15,20
2        3,10,15,20
3        3,15

And I would like to test if 3 and 15 are in the blob called NUMBERS. And can see the LIKE %% cannot be used

Only row with ID 2 and three scoulb be selected...

Answer 1

Not the most pretty solution, but it works:

select
   UID
from
   YOUR_TABLE
where
   find_in_set('3', cast(NUMBERS as char)) > 0
   and
   find_in_set('15', cast(NUMBERS as char)) > 0

Note that it's string comparison, so you may need to cast your input parameters to char as well.

Answer 2

This one also works:

SELECT * FROM table WHERE 3 IN (NUMBERS) AND 15 IN (NUMBERS)

using the IN will look into a comma separated string eg. these two

WHERE banana IN ('apple', 'banana', 'coconut')
WHERE 3 IN (2,3,6,8,90)

Based on info found on this page: http://www.webdevelopersnotes.com/tutorials/sql/tutorial_mysql_in_and_between.php3

Answer 3

Also check if this is helpful to anyone

An Extended function to eliminate the limitation of native FIND_IN_SET() in MySQL, this new extended version FIND_IN_SET_X() provides feature to compare one list with another list.

i.e.

mysql> SELECT FIND_IN_SET_X('x,c','a,b,c,d'); -> 3

http://forge.mysql.com/tools/tool.php?id=290