Query values ​​in comma separated string in MySQL

Question

I have a field COLORS (varchar(50)) in my table SHIRTS which contains a comma separated string like < ;code>1,2,5,12,15,. Each number represents an available color.

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

How should I rewrite the query so that it only selects all colors with the color 1 instead of all colors containing the number 1?

Answer 1

FIND_IN_SET is your friend in this case

select * from shirts where FIND_IN_SET(1,colors)

Answer 2

The classic method is to add commas on the left and right sides:

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

But find_in_set can also be used:

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