Find values in a comma separated string in a MySQL query

Question

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

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

Answer 1

You can add commas to the left and right:

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

But you can also try out the below option:

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