Problem
In my table SHIRTS, I have a field COLORS (varchar(50)) that contains a comma delimited string like 1,2,5,12,15. Each number represents a different hue.
When I run the query select * from shirts where colors like ‘percent 1 percent’ are used to retrieve all the red shirts (color=1), I also get the grey (=12) and orange (=15) shirts.
How should I update the query to choose only color 1 and not all colors with the number 1?
Asked by bikey77
Solution #1
Adding commas to the left and right is the traditional method:
select * from shirts where CONCAT(',', colors, ',') like '%,1,%'
However, find in set can also be used:
select * from shirts where find_in_set('1',colors) <> 0
Answered by Andomar
Solution #2
In this instance, FIND IN SET is your best friend.
select * from shirts where FIND_IN_SET(1,colors)
Answered by Shakti Singh
Solution #3
Take a look at the FIND_IN_SET function for MySQL.
SELECT *
FROM shirts
WHERE FIND_IN_SET('1',colors) > 0
Answered by Joe Stefanelli
Solution #4
This will undoubtedly work, and I have personally tested it:
lwdba@localhost (DB test) :: DROP TABLE IF EXISTS shirts;
Query OK, 0 rows affected (0.08 sec)
lwdba@localhost (DB test) :: CREATE TABLE shirts
-> (<BR>
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> ticketnumber INT,
-> colors VARCHAR(30)
-> );<BR>
Query OK, 0 rows affected (0.19 sec)
lwdba@localhost (DB test) :: INSERT INTO shirts (ticketnumber,colors) VALUES
-> (32423,'1,2,5,12,15'),
-> (32424,'1,5,12,15,30'),
-> (32425,'2,5,11,15,28'),
-> (32426,'1,2,7,12,15'),
-> (32427,'2,4,8,12,15');
Query OK, 5 rows affected (0.06 sec)
Records: 5 Duplicates: 0 Warnings: 0
lwdba@localhost (DB test) :: SELECT * FROM shirts WHERE LOCATE(CONCAT(',', 1 ,','),CONCAT(',',colors,',')) > 0;
+----+--------------+--------------+
| id | ticketnumber | colors |
+----+--------------+--------------+
| 1 | 32423 | 1,2,5,12,15 |
| 2 | 32424 | 1,5,12,15,30 |
| 4 | 32426 | 1,2,7,12,15 |
+----+--------------+--------------+
3 rows in set (0.00 sec)
Give it a Chance!!!
Answered by RolandoMySQLDBA
Solution #5
If the set of colors is more or less fixed, using string constants in your app and then using MySQL’s SET type with FIND IN SET(‘red’,colors) in your queries is the most efficient and also the most understandable option. When utilizing the SET type with FIND IN SET, MySQL stores all values in a single integer and checks for presence of values using a binary “and” operation, which is much faster than scanning a comma-separated string.
Internally, SET(‘red’,’blue’,’green’) stores’red’ as 1, ‘blue’ as 2, and ‘green’ as 4. The values’red,blue’ and’red,green’ would be saved as 3 (1|2) and 5 (1|4), respectively.
Answered by ColinM
Post is based on https://stackoverflow.com/questions/5033047/mysql-query-finding-values-in-a-comma-separated-string