CODEWITHSUNDEEP
sqlselectwhere-clause
Matt
Matt

Reputation: 153

How to have multiple NOT LIKE in SQL

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.

Upvotes: 3

Views: 25638

Answers (5)

Hopper
Hopper

Reputation: 146

If your select has two NOT LIKE criteria connected by an OR condition then everything will meet the criteria. In this situation "gmail" is not like "yahoo" and "yahoo" is not like "gmail" so even those two will pass the criteria. By converting the select to use the AND condition instead you capture those situations. The syntax requires you to provide the field name in both conditions. I feel that this code is easy to read and meets your needs.

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271241

As others have noted, the correct boolean connector with NOT LIKE is AND, not OR.

You might see the logic using NOT:

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

Upvotes: 0

mixkat
mixkat

Reputation: 3785

You just need to use AND instead of OR.

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

Upvotes: 4

Eray Balkanli
Eray Balkanli

Reputation: 8000

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

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133410

Two issue

you need the column name for each condition so add email after the OR 

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

and could be you want check for bot in the same time so you need AND 

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

Upvotes: 0

Related Questions