MySQL query finding values in a comma separated string

I have a field COLORS (varchar(50)) in a my table SHIRTS that contains a comma delimited string such as 1,2,5,12,15,. Each number representing the available colors.

When running the query select * from shirts where colors like '%1%' to get all the red shirts (color=1), I also get the shirts whose color is grey (=12) and orange (=15).

How should I rewrite the query so that is selects ONLY the color 1 and not all colors containing the number 1?

Solution 1:

The classic way would be to add commas to the left and right:

select * from shirts where CONCAT(',', colors, ',') like '%,1,%'

But find_in_set also works:

select * from shirts where find_in_set('1',colors) <> 0

Solution 2:

FIND_IN_SET is your friend in this case

select * from shirts where FIND_IN_SET(1,colors) 

Solution 3:

Take a look at the FIND_IN_SET function for MySQL.

SELECT * 
    FROM shirts 
    WHERE FIND_IN_SET('1',colors) > 0