Flo Flo - 1 year ago 65
MySQL Question

MySQL search in comma list

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


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?

awm awm
Answer Source

You need the FIND_IN_SET function:

SELECT ... WHERE FIND_IN_SET('1', field)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download