Search With Comma-Separated Value MySQL

MySQL query finding values in a comma separated string

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

search comma separated values from column contains comma separated string mysql

Like Jens has pointed out in the comments, it is highly recommended to normalize your schema.

If you wish to continue with string and comma separated values, you should then be looking at complex regex matching (which I leave it to you to explore).

However, one more alternative is to convert your column interest as JSON datatype. MYSQL 5.7 and above supports these datatypes.

CREATE TABLE IF NOT EXISTS `tbl` (
  `id` int(6) unsigned NOT NULL,
  `interest`  JSON DEFAULT NULL,
  `status` int(1) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `tbl` (`id`, `interest`,`status`) VALUES
  (1, '[1,2,3,4]',1),
  (2, '[1,2]',1),
  (3, '[3]',1);

And then query it as follows :

select id from tbl where JSON_CONTAINS( interest ,'[1,2]')
select id from tbl where JSON_CONTAINS( interest ,'[3,4]');
...

You can see it action in this sql fiddle.

Mysql query to search data based on comma separated string of ID's

A possible solution ...

select p.* , count(c.id) as cnt_match   
from product as p 
join category_table  c 
where 
      c.id in ( 1,3,7) and 
      instr(replace(replace(concat(',',p.categories,','),' ,',','),', ',','),
            concat(',',c.categorie_name,','))>0
group by p.id,p.product_name,p.categories;

And the result is 3 rows , TV 4 is a HD

+------+--------------+-----------------------+-----------+
| id   | product_name | categories            | cnt_match |
+------+--------------+-----------------------+-----------+
|    1 | TV 1         | LED, 32 inch, HD      |         2 |
|    3 | TV 3         | LCD , 24 inch, HD     |         2 |
|    4 | TV 4         | LED, 55 inch, Full HD |         1 |
+------+--------------+-----------------------+-----------+

AND because the data are not clean in the field categories , you need to add some replace to remove space before and after coma

How to search from comma separated string in comma separated column mysql

Use it like below let me know if it works

$values=array("1","2","3");

foreach($values as $val)
{
    $query="Select * from table_name where  FIND_IN_SET('".$val."',column_name)";
$result=mysql_query($query);
$data=mysql_fetch_array($result);
$dbval[]=$data['column_name'];

}

print_r($dbval);

how to select based on comma separated values in column

You can use the in-built find_in_set function.

find_in_set('s3',tags) > 0 and find_in_set('rds',tags) > 0

mysql check if numbers are in a comma separated list

This one also works:

SELECT * FROM table WHERE 3 IN (NUMBERS) AND 15 IN (NUMBERS)

using the IN will look into a comma separated string eg. these two

WHERE banana IN ('apple', 'banana', 'coconut')
WHERE 3 IN (2,3,6,8,90)

Info found on this page:

---

Related Topics