SQL "in" statement

Question

DB is MySQL. There is one table which structure is showed below:

select * from table1
id column1
-- --------
1  3,4,5
2  3,7,8

And sql which is not working correctly:

SELECT * FROM table1 WHERE 3 in (column1)

I know that the structure of the table is not right. But it was done before me. What can you suggest me in this way? or something that will perform the meaning of this sql? Thanks.

Answer 1

Use find_in_set It Returns position of value in string of comma-separated values

SELECT * 
FROM table1 
WHERE FIND_IN_SET(3, column1)

Answer 2

IN is a operator that you use to compare whether the left operand is in one of the values returned by a single column subquery If you want to use IN you would have to use WHERE 3,4,5 IN (column1)

Answer 3

Please normalize your table. Read why storing comma separated values in a db column is really bad.

If you are not allowed to, use FIND_IN_SET() function:

SELECT * 
FROM table1 
WHERE FIND_IN_SET(3, column1)

Answer 4

It looks like you want

SELECT * FROM table1 WHERE column1 LIKE "%,3,%" OR column1 LIKE "3,%" OR column1 LIKE "%,3"