Page 1 of 1

MySQL query help - searching an integer list

Posted: Sun Oct 31, 2004 6:37 am
by crabyars
Hi, I hope someone can help me. I've been looking half the night for a solution but I'm at a dead end.

I have a set of records, each has a field with a list of integers. I would like throw a list of integers at a search query and get matching rows.

example records:
1 - 1,3,5,8,10
2 - 2,51,59
3 - 4,9,10,23

So if I searched for "8,23,99" the query should return records 1 and 3.

I have tried "LIKE", I've tried "IN", and I've tried FULLTEXT searches. To use fulltext, I would have to change the minimum search length to 1, which feels like the wrong thing to do.

If anyone has any suggestions they will be much appreciated, thanks.

Posted: Sun Oct 31, 2004 10:38 am
by swdev
The best solution would be to re-design the table such that it didn't store multiple integers in a single field.

If that is not possible, then I suggest running 1 query for each integer to be found, in your example, this would mean running 3 queries.

Hope this helps

Posted: Sun Oct 31, 2004 1:38 pm
by crabyars
Thanks for your reply. I don't think I can run multiple queries there may be too many integers. I didn't think this should be so tricky as to call for a re-design. Hmmm