MySQL search in comma list [duplicate]

You need the FIND_IN_SET function:

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

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.

As others have said, Find_In_Set will let you write the query, but you really need to look at your database design (and I know you know this...)

The trouble with including Foreign Keys in a delimited list like this is that whole point of a foreign key is to enable you to locate the information in the other table quickly, using Indexes. By implementing a database as it sounds you have, you have all sorts of issues to resolve:

• How do I prevent duplicates (which would waste space)
• How do I remove a given value (Requires custom function, leading to possibility of errors?
• How do I respond to performance issues as the size of my tables increase?

There's only one truly acceptable way to address this - which is not to face the problem in the first place.

Have a sit down chat with those on high, and explain the problems with their solution - then explain the advantages of doing the job properly.

If they won't even discuss the point, look for a job with a decent employer who values your contributions.

Martin.

FIND_IN_SET is your best bet

 SELECT ... WHERE FIND_IN_SET(1,field_name)