MySQL Like multiple values

Question

I have this MySQL query.

I have database fields with this contents

sports,shopping,pool,pc,games 
shopping,pool,pc,games 
sports,pub,swimming, pool, pc, games   

Why does this like query does not work? I need the fields with either sports or pub or both?

SELECT * FROM table WHERE interests LIKE ('%sports%', '%pub%')

Answer 1

The (a,b,c) list only works with in. For like, you have to use or:

WHERE interests LIKE '%sports%' OR interests LIKE '%pub%'

Answer 2

Don't forget to use parenthesis if you use this function after an AND parameter

Like this:

WHERE id=123 and(interests LIKE '%sports%' OR interests LIKE '%pub%')

Answer 3

Like @Alexis Dufrenoy proposed, the query could be:

SELECT * FROM `table` WHERE find_in_set('sports', interests)>0 OR find_in_set('pub', interests)>0

More information in the manual.

Answer 4

Why not you try REGEXP. Try it like this:

SELECT * FROM table WHERE interests REGEXP 'sports|pub'

Answer 5

You can also use RLIKE as well.

For example:

SELECT * FROM TABLE_NAME WHERE COLNAME RLIKE 'REGEX1|REGEX2|REGEX3'

Answer 6

Or if you need to match only the beginning of words:

WHERE interests LIKE 'sports%' OR interests LIKE 'pub%'

you can use the regexp caret matches:

WHERE interests REGEXP '^sports|^pub'

https://www.regular-expressions.info/anchors.html