Mysql query to find multiple values ​​in a comma-separated list with AND clause

Question

I have the following table

Id | Userdefined01 ------------------------ 1 | 1,2,4,5 2 | 2,5 3 | 4,6,8 4 | 1,5 

I want to write a query to find all identifiers with values ​​2 and 5 in the userdefined01 field. Is it possible?

Answer 1

use FIND_IN_SET() - a built-in function for mysql to search for a string inside CSV.

 SELECT * FROM tableName WHERE FIND_IN_SET('2', Userdefined01) > 0 AND FIND_IN_SET('5', Userdefined01) > 0 

• SQLFiddle Demo
• MySQL FIND_IN_SET

If you have time to change the scheme, you need to denormalize it. If you look at an example, it could be a Many-to-Many relationship, so you can break it down into a 3-table design.