0

In my Database i have a field with a comma separated list of ids (itemids).

For an example i fill some rows with test data:

Row   |    itemids
1    =>    2,5,8,11,22,45
2    =>    4,6,9,13
3    =>    1,3,5,16,23
4    =>    6,12,18,21,24

Now i have a search string which contains all ids i want to serch for:

searchstring => 4,23,40

What i want to do now is to get all rows from the database which contains one or more values from my search string. From my example i should get the following rows:

Row   |    itemids
2    =>    4,6,9,13
3    =>    1,3,5,16,23

How do i do this? Can you give me an example SQL Query?

Thanks for help!

lhuber
  • 403
  • 1
  • 3
  • 13
  • 2
    Your table should be normalized. 1 row per id. Could explode the `searchstring` on `,`s and then do a like on each number but then `4` would match `14` and `24`. – chris85 Sep 06 '15 at 19:51
  • isn't it possible with my approch to have multiple id's per column? @chris85 – lhuber Sep 06 '15 at 19:53
  • 1
    Sure it is possible, it is going to be a pain though. http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – chris85 Sep 06 '15 at 19:56
  • and how can i realize it with my approach? @chris85 – lhuber Sep 06 '15 at 19:57
  • 1
    Say your first id were `3` instead of `4`. Your query would be `select stuff from table where id like '%3%' or id like '%24%' or id like '%40%'`. This will bring back rows `2`, `3`, and `4`. Row 3 is returned because `23` matches that like (`'%3%' `). Where as if these ids all had their own records you could do something like `select stuff from table where id in (4,23,40)`. – chris85 Sep 06 '15 at 20:04
  • Or use full text search by `select stuff from table where concat(',',id,',') like '%,3,%' or concat(',',id,',') like '%,24,%' or concat(',',id,',') like '%,40,%'` – splash58 Sep 06 '15 at 20:07
  • We are over-engineering it. Its simple and it has been answered. `find_in_set` function is handy in such requirements... And please upvote if you like the simplicity of the answer.. :) – seahawk Sep 06 '15 at 20:13

1 Answers1

0

Query for searchstring => 4,23,40

select * from
item_table
where find_in_set(4,itemids)>0

UNION  

select * from
item_table
where find_in_set(23,itemids)>0

UNION

select * from
item_table
where find_in_set(40,itemids)>0
seahawk
  • 1,872
  • 12
  • 18