CODEWITHSUNDEEP
mysqlwhere-insql-in
userlond
userlond

Reputation: 3818

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?

Upvotes: 1

Views: 86

Answers (1)

Beginner
Beginner

Reputation: 4153

try this brother

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

Upvotes: 2

Related Questions