CODEWITHSUNDEEP
mysqlsqlwildcardsql-like
user3747727
user3747727

Reputation: 21

How can I filter out specific email addresses?

I am using MySQL to query a table of customer details called "clients", which includes a column for email addresses called "email". I would like to exclude all email addresses from gmail, hotmail and yahoo. I have tried writing the query (see below for my latest) but I cannot seem to get the use of NOT LIKE correct or find an alternative.

Can someone please help?

MySQL Query:

SELECT      name, email

FROM        clients

WHERE       email NOT LIKE '%gmail%'
    OR  email NOT LIKE '%hotmail%'
    OR  email NOT LIKE '%yahoo%'
;

End

P.S. I have searched for existing questions but no luck in finding the answer I need. Apologies if one already exists and I would appreciate you pointing me in the right direction if you find one. I'm a newbie to all of this, as I started learning SQL a few months ago for work.

Upvotes: 2

Views: 9259

Answers (1)

Novocaine
Novocaine

Reputation: 4786

You were nearly there, just replace the OR's with ANDs

SELECT name, email
FROM clients
WHERE email NOT LIKE '%gmail%' AND email NOT LIKE '%hotmail%' AND email NOT LIKE '%yahoo%';

By using AND it means that every condition needs to be met. Using just OR means that any condition can be true, but because you're also using NOT LIKE as well technically every row will match your conditions.

e.g. a gmail row matches the hotmail and yahoo rules individually and will therefore be returned in the results because of the OR as any rules can be matched.

Upvotes: 5

Related Questions