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 who's 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?
解决方案
The classic way would be to add comma's to the left and right:
select * from shirts where ',' + colors + ',' like '%,1,%'
But find_in_set also works:
select * from shirts where find_in_set('1',colors) <> 0