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.

Use the `FIND_IN_SET()` function. But only until you normalize your table. — ypercubeᵀᴹ, Jan 21 '12 at 09:29
Storing comma separated values in db columns is never good: [Is it that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-comma-separated-list-in-a-database-column-really-that-bad) — ypercubeᵀᴹ, Jan 21 '12 at 09:30

score 5 · Accepted Answer · edited May 23 '17 at 11:56

5

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)

edited May 23 '17 at 11:56

Community

1
1

answered Jan 21 '12 at 09:32

ypercubeᵀᴹ

113,259
19
174
235

I know that it is bad. This structure was created before me and by normalizing the table we have to change a lot of sql statements in application. Thank you, ypercube. And one more question please :) Is FIND_IN_SET() better than using LIKE '%,3,%'(ofcourse after adding commas to the beginning end to the end)? Because there will be over 500.000 records in this table. – Teymur Hacizade Jan 21 '12 at 09:52
With 500K rows, both will be slow. You'll have to test but I think `FIND_IN_SET()` will be faster than `CONCAT`+`LIKE` or `LIKE OR LIKE` solution. – ypercubeᵀᴹ Jan 21 '12 at 09:57

score 1 · Answer 2 · answered Jan 21 '12 at 09:30

1

It looks like you want

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

answered Jan 21 '12 at 09:30

Luchian Grigore

253,575
64
457
625

score 1 · Answer 3 · answered Jan 21 '12 at 09:33

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)

answered Jan 21 '12 at 09:33

score 0 · Answer 4 · answered Jan 21 '12 at 09:33

0

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)

answered Jan 21 '12 at 09:33

Luis

1,294
7
9

SQL "in" statement

4 Answers4