Select where in (field which is an imploded array)

Question

I have a field in my database table which is an imploded array of whole numbers that uses commas as glue. These numbers represent ID's for rows within another table.

For example, this field can contain any of the following values:

2 2,5,12 5,6 14 4,12,55 etc.

How would I do a search for all rows which contain, say, "5" in said field?

Lets say I want to find the rows which contain "5", so it would find rows with any of the following for said field:

5 5,12 3,5,8 1,4,5 etc.

Any help is greatly appreciated.

possible duplicate of [Can Mysql Split a column?](http://stackoverflow.com/questions/1096679/can-mysql-split-a-column) — Niloct, Oct 11 '14 at 01:47
yes it is the same requirement, but August 2009 is a long time ago for MySQL. — Paul Maxwell, Oct 11 '14 at 01:55

score 0 · Accepted Answer · answered Oct 11 '14 at 01:56

0

Use find_in_set()

mysql> SELECT FIND_IN_SET('b','a,b,c,d');
        -> 2

see: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

However, I don't recommend storing non-normalised data if you are searching for it frequently in particular. Normalised tables of such data can take advantage of indexes.

answered Oct 11 '14 at 01:56

Paul Maxwell

33,002
3
32
51

I just found it but thank you :) Will mark as solved. – inkd Oct 11 '14 at 02:02
no problem, the tick may may help other find it too :) – Paul Maxwell Oct 11 '14 at 03:29

Select where in (field which is an imploded array)

1 Answers1