Mysql IN statement only find the first element in the list

Question

I have a table named table and one of the rows in table has a value like this: 6,7,8,9

I want to select this row and this is the code I expect to work:

SELECT * FROM table WHERE 8 IN (column)

It doesn't work. However the code which works confuses me:

SELECT * FROM table WHERE 6 IN (column)

I tried with several values and found out that only the first element of the list is working.

How can this be?

Take a look at [this answer](http://stackoverflow.com/a/10953029/259457). I think it will do what you want. — Travesty3, Aug 31 '12 at 12:43

score 8 · Accepted Answer · answered Aug 31 '12 at 12:41

8

Because you are comparing number with string. 6,7,8,9 cast to number will be 6, so it will be true when do 6 IN (column).

You need to use FIND_IN_SET function:

SELECT * FROM `table` WHERE FIND_IN_SET(8, `column`);

answered Aug 31 '12 at 12:41

xdazz

1 Answers1