Advertisement
Advertisement


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%')
2017/08/19
1
146
8/19/2017 7:07:08 PM

Accepted Answer

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

WHERE interests LIKE '%sports%' OR interests LIKE '%pub%'
2010/11/13
133
11/13/2010 11:10:32 AM


Why not you try REGEXP. Try it like this:

SELECT * FROM table WHERE interests REGEXP 'sports|pub'
2016/01/16

You can also use REGEXP's synonym RLIKE as well.

For example:

SELECT *
FROM TABLE_NAME
WHERE COLNAME RLIKE 'REGEX1|REGEX2|REGEX3'
2020/08/17

Your query should be SELECT * FROM `table` WHERE find_in_set(interests, "sports,pub")>0

What I understand is that you store the interests in one field of your table, which is a misconception. You should definitively have an "interest" table.

2018/01/23

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%')
2017/05/08

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

2019/09/26

Source: https://stackoverflow.com/questions/4172195
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]