How to have multiple NOT LIKE in SQL

Question

I have two tables in a database. Contacts and Filter

In Contacts, I have ID, Name, and Email as the fields.

in Filter, I have ID and code

My objective is to be able to query the entire table and export a list that has been filtered by items in the Filter table. (basically the same that could be achieved with a grep -i -Ev ) ... Basically I want to filter out gmail or yahoo or others).

So if I do

select distinct email from contacts where email not like '%gmail%'

One level of the filter works. but if I do,

select distinct email from contacts where email not like '%gmail%' or not like '%yahoo%'

then things start to fail.

Before I start to integrate the nested select code in filter, I cannot get the multiple where field not like X or field1 not like Y working.

Any input is greatly appreciated.

sample data

name           email 
bob            [email protected]
joey           [email protected]

desired output

[email protected]

UPDATE: Thank you all for your help. Answer to phase I of the question was to change from OR to AND. :)

Phase II: Instead of having a query that is larger and larger,.. I would rather use a query determine the items to exclude (meaning if any of them match, then exclude them).. so I would then add yahoo gmail protonmail to records in the code field of the filters table.. with that would it be

select distinct email from contacts where email not like in (select code from filters)

This fails as it says that the select has multiple records

UPDATE:

SELECT DISTINCT email FROM Contacts WHERE email NOT LIKE (select filters.code from filters where filters.id=4)

works.. but is only pulling one record as the filter. not all of them as filters.

Answer 1

You just need to use AND instead of OR.

SELECT distinct email 
FROM 
    contacts 
WHERE 
    email not like '%gmail%' 
    AND email not like '%yahoo%'

Answer 2

You can benefit from CHARINDEX like below, I think this will increase the performance of your query. Also, you can use group by instead of distinct, it will also help the performance.

select email 
from contacts 
where charindex('gmail',email) < 1 
  and charindex('yahoo',email) < 1
group by email