CODEWITHSUNDEEP
sqlmysqldatabase
bikey77
bikey77

Reputation: 6672

MySQL query finding values in a comma separated string

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?

Upvotes: 112

Views: 198975

Answers (12)

Ergest Basha
Ergest Basha

Reputation: 8973

The correct answer would be to fix the table design by splitting the comma separated string into rows.

If the schema can not be changed , you could try the following approach which requires MySQL 8+.

We can use JSON_TABLE to split comma-separated values dynamically.

Consider the following data example

CREATE TABLE shirts (
    id INT PRIMARY KEY,
    colors VARCHAR(50) NOT NULL
);

INSERT INTO shirts VALUES (1,'1,2,3'), (2,'2,3,4'), 
  (3,'3,4'), (4,'1,2'), (5,'12,14');

Split the colors

SELECT s.id,
       j.colors
FROM shirts s
JOIN JSON_TABLE(
    CONCAT('[\"', REPLACE(s.colors, ',', '\",\"'), '\"]'),  
    '$[*]' COLUMNS (colors VARCHAR(10) PATH '$')  
) AS j

Retrieve all id that have colors "1" included

 SELECT s.id,
        j.colors
 FROM shirts s
 JOIN JSON_TABLE(
                CONCAT('[\"', REPLACE(s.colors, ',', '\",\"'), '\"]'),  
                       '$[*]' COLUMNS (colors VARCHAR(10) PATH '$')  
                 ) AS j
 WHERE j.colors = 1;

Above query we could use as a JOIN to retrieve all columns from the table

SELECT sh.*
FROM shirts sh
INNER JOIN (SELECT s.id,
                   j.colors
            FROM shirts s
            JOIN JSON_TABLE(
                          CONCAT('[\"', REPLACE(s.colors, ',', '\",\"'), '\"]'),  
                                 '$[*]' COLUMNS (colors VARCHAR(10) PATH '$')  
                          ) AS j
            WHERE j.colors = 1
           ) sub on sub.id=sh.id;

See example

Upvotes: 0

Delickate
Delickate

Reputation: 1120

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');

Upvotes: 1

Saranga kapilarathna
Saranga kapilarathna

Reputation: 644

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, ','))

Upvotes: 0

Andomar
Andomar

Reputation: 238086

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

Upvotes: 220

Deepak Bhatta
Deepak Bhatta

Reputation: 474

select * from shirts where find_in_set('1',colors) <> 0

Works for me

Upvotes: 4

ColinM
ColinM

Reputation: 13936

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).

Upvotes: 7

Backslider
Backslider

Reputation: 7

All the answers are not really correct, try this:

select * from shirts where 1 IN (colors);

Upvotes: -11

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

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

Upvotes: 12

Shakti Singh
Shakti Singh

Reputation: 86386

FIND_IN_SET is your friend in this case

select * from shirts where FIND_IN_SET(1,colors)

Upvotes: 40

CanSpice
CanSpice

Reputation: 35798

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

Upvotes: 3

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

Take a look at the FIND_IN_SET function for MySQL.

SELECT * 
    FROM shirts 
    WHERE FIND_IN_SET('1',colors) > 0

Upvotes: 30

KOGI
KOGI

Reputation: 3989

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'

Upvotes: 3

Related Questions