Query values ​​in comma separated string in MySQL
P粉763748806
P粉763748806 2023-08-17 14:24:54
0
2
453
<p>I have a field <code>COLORS (varchar(50))</code> in my table <code>SHIRTS</code> which contains a comma separated string like < ;code>1,2,5,12,15,</code>. Each number represents an available color. </p> <p>When running the query <code>select * from shirts where colors like '%1%'</code> to get all shirts that are red (color=1), I also get the color is gray (= 12) and orange (=15) shirts. </p> <p>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? </p>
P粉763748806
P粉763748806

reply all(2)
P粉036800074

FIND_IN_SET is your friend in this case

select * from shirts where FIND_IN_SET(1,colors)
P粉254077747

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
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template