mysql HELP with : ... WHERE value IN (column)

[humandesigner]

Hello there :)

Well ... I'm currently having a nasty headache *thanks to* a problem I can't find a workaround for.

Here it is :

SELECT * FROM table WHERE "6" IN (user_IDs)

would retrieve only the row where user_IDs = 6 but not the ones where user_IDs = 5,6,7 for example.

How do I do if I want to retrieve all the rows where 6 can be found in user_IDs? (as you've noticed, user_IDs is an array stored as a delimited string).

The main problem is that doing

SELECT * FROM table WHERE user_IDs LIKE '%6%'

would retrieve rows where user_IDs = 5,66,7 which would be wrong.

Is there a solution to this problem?
Thanks for helping :)

[jollymcfats]

You could use a regexp. If you use the word boundry modifiers, it will match a 6 only between two commas, or at the head or tail of the string.

SELECT * FROM table WHERE user_IDs REGEXP '[[:<:]]6[[:>:]]';

[coopster]

Or how about FIND_IN_SET [webmasterworld.com]?