CODEWITHSUNDEEP
sqlsqlite
sonfollower
sonfollower

Reputation: 1107

How do I add multiple "NOT LIKE '%?%' in the WHERE clause of sqlite3?

I have a sqlite3 query like:

SELECT word FROM table WHERE word NOT LIKE '%a%';

This would select all of the words where 'a' does not occur in the word. This I can get to work perfectly. The problem is if I want to further restrict the results to not include 'b' anywhere in the word. I am picturing something like this.

SELECT word FROM table WHERE word NOT IN ('%a%', '%b%', '%z%');

which this obviously does not work, but this is the idea. Just adding an AND clause is what I'm trying to avoid:

SELECT word FROM table WHERE word NOT LIKE '%a%' AND NOT LIKE '%b%';

If this is the only option then I will have to work with that, but I was hoping for something else.

Upvotes: 67

Views: 398071

Answers (8)

Damith Karunarathne
Damith Karunarathne

Reputation: 3

Try out below query which worked for me.

SELECT word FROM table WHERE  NOT (word LIKE '%a%' AND word LIKE '%b%');

Upvotes: -1

Vipin Pandey
Vipin Pandey

Reputation: 907

this is a select command 

   FROM
    user
WHERE
    application_key = 'dsfdsfdjsfdsf'
        AND email NOT LIKE '%applozic.com'
        AND email NOT LIKE '%gmail.com'
        AND email NOT LIKE '%kommunicate.io';

this update command 

 UPDATE user
    SET email = null
    WHERE application_key='dsfdsfdjsfdsf' and  email not like '%applozic.com' 
    and email not like '%gmail.com'  and email not like '%kommunicate.io';

Upvotes: 8

Etherlord
Etherlord

Reputation: 81

You missed the second statement: 1) NOT LIKE A, AND 2) NOT LIKE B

SELECT word FROM table WHERE word NOT LIKE '%a%' AND word NOT LIKE '%b%'

Upvotes: 8

David Hallam
David Hallam

Reputation: 41

The query you are after will be 

SELECT word FROM table WHERE word NOT LIKE '%a%' AND word NOT LIKE '%b%'

Upvotes: 4

Dariusz
Dariusz

Reputation: 9

If you have any problems with the "not like" query, Consider that you may have a null in the database. In this case, Use: 

IFNULL(word, '') NOT LIKE '%something%'

Upvotes: 0

Raymond Hettinger
Raymond Hettinger

Reputation: 226336

I'm not sure why you're avoiding the AND clause. It is the simplest solution.

Otherwise, you would need to do an INTERSECT of multiple queries:

SELECT word FROM table WHERE word NOT LIKE '%a%'
INTERSECT
SELECT word FROM table WHERE word NOT LIKE '%b%'
INTERSECT
SELECT word FROM table WHERE word NOT LIKE '%c%';

Alternatively, you can use a regular expression if your version of SQL supports it.

Upvotes: 4

Michael Low
Michael Low

Reputation: 24506

If you use Sqlite's REGEXP support ( see the answer at Problem with regexp python and sqlite for how to do that ) , then you can do it easily in one clause:

SELECT word FROM table WHERE word NOT REGEXP '[abc]';

Upvotes: 30

laher
laher

Reputation: 9110

SELECT word FROM table WHERE word NOT LIKE '%a%' 
AND word NOT LIKE '%b%' 
AND word NOT LIKE '%c%';

Upvotes: 95

Related Questions