userlond userlond - 2 months ago 5
MySQL Question

Mysql search in concatinated string

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?

Answer

try this brother

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