109

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?

danronmoon
  • 3,814
  • 5
  • 34
  • 56
bikey77
  • 6,384
  • 20
  • 60
  • 86
  • 6
    You could do this via regex, I suppose, but the much better solution would be to break shirt colors into a separate table (colors) and use a join table (shirt_colors) using the ids of color/shirt to link them. – ceejayoz Feb 17 '11 at 18:36
  • I can't believe with 6 answers *none* of them mentioned MySQL's SET data type.. – ColinM Dec 14 '12 at 15:30
  • 1
    check this: http://stackoverflow.com/questions/12559876/finding-exact-value-from-a-comma-separated-string-in-php-mysql – Alireza Jun 28 '14 at 06:55

11 Answers11

218

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
Don't Panic
  • 41,125
  • 10
  • 61
  • 80
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • I tried find_in_set but it returns the same result no matter the color value I enter... Any suggestions? – bikey77 Feb 17 '11 at 19:19
  • @bikey77: Maybe this is the problem, the [documentation](http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set) says: This function does not work properly if the first argument contains a comma (“,”) character. – Andomar Feb 17 '11 at 19:27
  • My wrong, it was a logical mistake due to the same dummy values. It works fine. Thanks! – bikey77 Feb 17 '11 at 19:37
  • @Andomar Before i found your answer i wast struggling with IN but yours is work like a charm... Thank you so much.. – Gautam Arya Oct 19 '12 at 20:35
  • 4
    It has a performance impact as Find_in_set doesn't used index – Kamran Shahid May 29 '15 at 09:15
  • @KamranShahid would it be possible to index the field fulltext to alleviate the performance hit? – r3wt Jun 23 '17 at 18:38
  • Is this method better than REGEXP? WHERE color REGEXP "(^|,)1(,|$)"? – user752746 Mar 25 '20 at 19:04
39

FIND_IN_SET is your friend in this case

select * from shirts where FIND_IN_SET(1,colors)
Shakti Singh
  • 84,385
  • 21
  • 134
  • 153
30

Take a look at the FIND_IN_SET function for MySQL.

SELECT * 
    FROM shirts 
    WHERE FIND_IN_SET('1',colors) > 0
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
12

This will work for sure, and I actually tried it out:

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 Try !!!

Shakti Singh
  • 84,385
  • 21
  • 134
  • 153
RolandoMySQLDBA
  • 43,883
  • 16
  • 91
  • 132
  • 1
    Hey @rolandomysqldba, I test your query and it works ok but I need to make some changes in it. Let's say if I want to get all the shirts where color value is 1,2 in the column. – Ahsan Saeed Jan 24 '20 at 04:32
7

If the set of colors is more or less fixed, the most efficient and also most readable way would be to use string constants in your app and then use MySQL's SET type with FIND_IN_SET('red',colors) in your queries. When using the SET type with FIND_IN_SET, MySQL uses one integer to store all values and uses binary "and" operation to check for presence of values which is way more efficient than scanning a comma-separated string.

In SET('red','blue','green'), 'red' would be stored internally as 1, 'blue' would be stored internally as 2 and 'green' would be stored internally as 4. The value 'red,blue' would be stored as 3 (1|2) and 'red,green' as 5 (1|4).

tremby
  • 9,541
  • 4
  • 55
  • 74
ColinM
  • 13,367
  • 3
  • 42
  • 49
4
select * from shirts where find_in_set('1',colors) <> 0

Works for me

Deepak Bhatta
  • 474
  • 4
  • 16
3

If you're using MySQL, there is a method REGEXP that you can use...

http://dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp

So then you would use:

SELECT * FROM `shirts` WHERE `colors` REGEXP '\b1\b'
KOGI
  • 3,959
  • 2
  • 24
  • 36
3

You should actually fix your database schema so that you have three tables:

shirt: shirt_id, shirt_name
color: color_id, color_name
shirtcolor: shirt_id, color_id

Then if you want to find all of the shirts that are red, you'd do a query like:

SELECT *
FROM shirt, color
WHERE color.color_name = 'red'
  AND shirt.shirt_id = shirtcolor.shirt_id
  AND color.color_id = shirtcolor.color_id
CanSpice
  • 34,814
  • 10
  • 72
  • 86
  • 8
    @Blindy: That's only true if you assume the OP has edit rights on the database schema; has the time to redesign the database, migrate the data, and refactor all clients; and that the reduction of complexity for this query outweighs the increase in complexity in the rest of the application. – Andomar Feb 17 '11 at 19:30
  • 1
    @Andomar, then again when he'll run into size restrictions for row retrievals and his "records" will get clipped, THAT's when the real fun will begin! – Blindy Feb 17 '11 at 21:11
  • 3
    @Blindy: You're missing the point; I'm not arguing that he has the best solution, just that not everyone has the freedom to redesign his environment to his liking – Andomar Feb 17 '11 at 21:16
  • I agree with @Andomar – Adam B Oct 09 '13 at 23:29
1

You can achieve this by following function.

Run following query to create function.

DELIMITER ||
CREATE FUNCTION `TOTAL_OCCURANCE`(`commastring` TEXT, `findme`     VARCHAR(255)) RETURNS int(11)
NO SQL
-- SANI: First param is for comma separated string and 2nd for string to find.
return ROUND (   
    (
        LENGTH(commastring)
        - LENGTH( REPLACE ( commastring, findme, "") ) 
    ) / LENGTH(findme)        
);

And call this function like this

msyql> select TOTAL_OCCURANCE('A,B,C,A,D,X,B,AB', 'A');
Dharman
  • 30,962
  • 25
  • 85
  • 135
Delickate
  • 1,102
  • 1
  • 11
  • 17
  • if it is counting properly, you should never need to use `ROUND`... if it doesn't return a whole number then there's some other issue – ashleedawg Oct 14 '22 at 04:10
0

1. For MySQL:

SELECT FIND_IN_SET(5, columnname) AS result 
FROM table

2.For Postgres SQL :

SELECT * 
FROM TABLENAME f
WHERE 'searchvalue' = ANY (string_to_array(COLUMNNAME, ','))

Example

select * 
from customer f
where '11' = ANY (string_to_array(customerids, ','))
Saranga kapilarathna
  • 564
  • 1
  • 12
  • 25
-10

All the answers are not really correct, try this:

select * from shirts where 1 IN (colors);
Backslider
  • 9
  • 3