Mysql search in concatinated string

Question

There is a table:

CREATE TABLE n_dummy (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  `values` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

INSERT INTO `n_dummy` (id, `values`) VALUES
(2, '2,10'),
(10, '2,10')
(3,  '7,3');

Look like this:

id | values
-----------
2  | 2,10
10 | 2,10
3  | 7,3

Fiddle included.

The first column is the integer to be searched in string from values column.

Note: Example is too simplified and looks stupid. Refactoring table structure is not the way. Only sql query with standard functions and procedures.

I want to search integer value in string, concatenated from integers with , separator.

I expects MySQL to do this with IN operator: SELECT id FROM n_dummy WHERE id IN(values);

And result would be 2, 10 and 3. But MySQL returns only 2, the second and other values is not searchable with IN operator with string.

How to search integer in concatenated string using sql-query and prebuild routines?

Question is too simplified version of very big query (%), which consists from a huge ammount of subqueries, so the actual tables are created dynamically, not with insert statement. — userlond, Sep 14 '16 at 02:57
The main purpose of question for me is to know, how to find integer in dynamically created concatenated with separator string (it is formed via `CONCAT_WS` function and many `CASE` operators, not plain sql-query) — userlond, Sep 14 '16 at 03:00
Sorry completely wrong design. One does not store comma separated values in columns — e4c5, Sep 14 '16 at 03:08
Values is not stored actually, whey formed dynamically based on complicated case condition. Bad design, ok, let it be so :) — userlond, Sep 14 '16 at 03:13

score 2 · Accepted Answer · answered Sep 14 '16 at 03:14

2

try this brother

SELECT * FROM `n_dummy` WHERE concat(',',`values`,',') LIKE concat('%',',',`id`,',','%')

answered Sep 14 '16 at 03:14

Beginner

4,118
3
17
26

Mysql search in concatinated string

1 Answers1