LIKE clause that will select '2' but not '12' from a string of characters

I have a legacy database that includes a field 'User_Defined' containing a string describing which projects each record belongs to. The project IDs are comma-separated numbers in that string. For example, records might include:

2
12
22
2,12
2,8,10,12
2,12,2开发者_运维百科0,120,220

The current query is something along the lines of:

SELECT tbl_species.Species_ID, 
    tbl_species.Common_Name, 
    tbl_species.User_Defined
FROM tbl_species
WHERE User_Defined LIKE '%2';

In this case, the query of course would return all the records that contain '2' anywhere in the string. I'm picking on 2 here, but it can be any number passed in via a PHP variable.

My question is how can I craft the query so that it returns only those records containing 2 but not 12, 22, 120, etc.?

BTW, I realize that the proper way to fix this would be to create a lookup table to handle this relation, but if I could patch this until the next redesign, it would be great!

Thanks!

Try using FIND_IN_SET:

WHERE FIND_IN_SET('2', User_Defined)

Add a leading and trailing comma to the column being searched, then search for your value surrounded by commas.

...
WHERE CONCAT(',', User_Defined, ',') LIKE '%,2,%';

Try this:

User_Defined LIKE '2,%' OR User_Defined='2' OR User_Defined LIKE '%,2' OR User_Defined LIK E '%,2,%'