CODEWITHSUNDEEP
mysqlsqlsql-like
webmasters
webmasters

Reputation: 5831

MySQL Like multiple values

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%')

Upvotes: 208

Views: 452586

Answers (9)

Intacto
Intacto

Reputation: 557

More work examples:

SELECT COUNT(email) as count FROM table1 t1 
JOIN (
      SELECT company_domains as emailext FROM table2 WHERE company = 'DELL'
     ) t2 
ON t1.email LIKE CONCAT('%', emailext) WHERE t1.event='PC Global Conference';

Task was count participants at an event(s) with filter if email extension equal to multiple company domains.

Upvotes: 3

jazkat
jazkat

Reputation: 5788

Faster way of doing this:

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

is this: 

WHERE interests REGEXP 'sports|pub'

Found this solution here: http://forums.mysql.com/read.php?10,392332,392950#msg-392950

More about REGEXP here: http://www.tutorialspoint.com/mysql/mysql-regexps.htm

Upvotes: 434

iamharish15
iamharish15

Reputation: 1850

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

For example:

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

Upvotes: 36

vadim
vadim

Reputation: 1043

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

Upvotes: 18

Franc Drobnič
Franc Drobnič

Reputation: 1085

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.

Upvotes: 2

Alexis Dufrenoy
Alexis Dufrenoy

Reputation: 11946

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.

Upvotes: 8

Luan
Luan

Reputation: 111

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%')

Upvotes: 11

Ahmad Hussain
Ahmad Hussain

Reputation: 2491

Why not you try REGEXP. Try it like this:

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

Upvotes: 44

Andomar
Andomar

Reputation: 238048

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

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

Upvotes: 162

Related Questions