Menu
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?
812
MySQL has an inbuilt function called FIND_IN_SET which will search for values within a comma separated values. It basically returns the index position of the first parameter within the second parameter. This can be alternatively used to replace the IN clause. WHERE FIND_IN_SET (item_description,'Mobile,laptop');
To check if value exists in a comma separated list, you can use FIND_IN_SET() function. Now you can insert some records in the table using insert command. Display all records from the table using select statement.
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 FIND_IN_SET is your friend in this case
select * from shirts where FIND_IN_SET(1,colors) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
