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)

How are you, dear friend? I have to write a query that will search line by line and see if there are numbers 20,50 and 59 in each line, and so the sort three numbers 20,50 and 59 are different in each line. — Hossein, Nov 12 '22 at 06:02

score 0 · Answer 1 · answered Nov 12 '22 at 07:03

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.

Multiple search with FIND_In_SET

1 Answers1