MySQL search in comma list [duplicate]

Question

I have a MySQL field with a reference to another table where ids are saved as comma seperated list, eg:

12,13,14,16 

which stand for values in another table. I know this is very bad and wrong, but this comes from above and I cant do anything about that. The problem now is that i want to search in that field with a query like this:

SELECT ... WHERE field LIKE '%1%' 

The Problem now is obviously that almost all entries can be found with this example Query, because the most common IDs are in Range 10-20. My Idea is to search for %,1,% instead, but this does not work for the first and last id in the field. Ist there something like an internal replace or how do i fix this the best way?

Answer 1

You need the FIND_IN_SET function:

SELECT ... WHERE FIND_IN_SET('1', field) 

Answer 2

Be aware that plain FIND_IN_SET is case-insensitive,

i.e. FIND_IN_SET('b3','a1,a2,B3,b3') and FIND_IN_SET('B3','a1,a2,B3,b3') both return 3.

To be case sensitive, add 'binary' modifier to the 1st argument, e.g. FIND_IN_SET (binary 'b3', 'a1,a2,B3,b3') returns 4.