CODEWITHSUNDEEP
phpmysql
MTALY
MTALY

Reputation: 1772

How to use LIKE operator against multiple values

I have this query :

select * from users where mailaddress  
  NOT like '%banned_domain1.com%'  
    AND mailaddress NOT like '%banned_domain2.com%'  
      AND mailaddress NOT like '%banned_domain3.com%' ;

I want to make it more simple , I executed this query from command line :

select * from users where mailaddress NOT like ('%banned_domain1.com%','%banned_domain2.com%','%banned_domain3.com%') ;

I got MySQL error :

ERROR 1241 (21000): Operand should contain 1 column(s)

Upvotes: 0

Views: 384

Answers (4)

Bruno Domingues
Bruno Domingues

Reputation: 43

Instead of "Like" use "In" and format the email address like this:

select * from users where SUBSTR(mailaddress, INSTR(mailaddress, '@') + 1)
NOT IN ('banned_domain1.com','banned_domain2.com','banned_domain3.com');

The SUBSTR will remove the @ and anything preceding it, leaving only the domain name then you can do a perfect comparison without wildcards using IN.

Cheers!

Upvotes: 1

Lkopo
Lkopo

Reputation: 4825

You can use NOT REGEXP

SELECT * FROM users WHERE mailaddress NOT REGEXP 'banned_domain1.com|banned_domain2.com|banned_domain3.com';

See live demo

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

You cannot simplify your query. You need one LIKE per condition.

Upvotes: 0

Khan Shahrukh
Khan Shahrukh

Reputation: 6361

you have to mention the column every time 

select * from tasks where title NOT LIKE '%eating lunch%' AND title NOT LIKE '%eating breakfast%' AND title NOT LIKE '%a new task%'

however as Bruno Domingues said use NOT IN that will be more easy

Upvotes: 0

Related Questions