mysql query to search multiple values in comma separated list with AND clause

Question

I have 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 Ids having values 2 AND 5 in userdefined01 field. Is that possible?

Answer 1

use FIND_IN_SET() -- a builtin function for mysql to search a string inside a 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 alter the schema, you need to denormalize it. Looking on the example, it may be Many-to-Many relationship so you can break it into 3-table design.