Multiple search with FIND_In_SET

Question

My database structure is as follows :

id    values
1     10,15,50,89,200,590
2     30,50,89,45,20
3     15,20,40,50,10,500
4     20,30,90,89,50

I want to write a query to filter the data as follows :

2     30,50,89,45,20
4     20,30,90,89,50

That is, find all the lines that have the numbers 20, 50, and 89

My efforts to solve this problem :

WHERE FIND_IN_SET("20", values)

Answer 1

If - according to your description - all numbers must occur, just repeat your condition for all of them:

SELECT id, yourcolumn
FROM yourtable
WHERE FIND_IN_SET(20,yourcolumn)
AND FIND_IN_SET(50,yourcolumn)
AND FIND_IN_SET(89,yourcolumn);

If just at least one of them must occur, you could replace the "AND" by "OR"...

SELECT id, yourcolumn
FROM yourtable
WHERE FIND_IN_SET(20,yourcolumn)
OR FIND_IN_SET(50,yourcolumn)
OR FIND_IN_SET(89,yourcolumn);

OR you can use a regex like this which is much shorter:

SELECT id, yourcolumn
FROM yourtable
WHERE CONCAT(",", `yourcolumn`, ",") REGEXP ",(20|50|89),";

1st note: I renamed the column "values" in my queries because SQL key words should not be used as column name.

2nd note: It would be much better to normalize your data, meaning creating separate columns to apply separate conditions or values instead of using comma-separated lists.