MySQL Query Finding Values in a Comma Separated String

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 for ALL Comma Separated Values In String

In MySQL, you can use handy string function find_in_set() to search for a value within a comma-separated string:

select * from recipes where find_in_set(id, ?) order by name

Note that I rewrote your statement as a parameterized query rather than munging values inside the query string. You do want to use these to make your code more efficient and safer.

Also, please keep in mind that this is rather inefficient way to proceed. It would be far beter to split your list into individual values, and to use in:

where id in (12, 13, 34, 65, 11)

As a parameterized query:

where id in (?, ?, ?, ?, ?)

MySQL - Find number in string of comma-separated numbers

You may use FIND_IN_SET here:

SELECT *
FROM users
WHERE FIND_IN_SET('blockedid', blockeduserids) = 0;

But, in general, it is not desirable to persist comma separated strings in your database tables. A better design would be to have each blocked user id in a separate table. If you had such a separate table, you could write:

SELECT *
FROM users u
WHERE NOT EXISTS (SELECT 1 FROM blockeduserstable b WHERE u.user_id = b.user_id);

This would likely greatly outperform your current approach.

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

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:

MYSQL - get value from position of comma-separated-string

You can use substring_index twice in this way (change 2 to whichever item you want to extract):

select substring_index(substring_index(col, ',', 2), ',', - 1)
from t

Demo

---

Related Topics