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

Question

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.

It really is sqlite3 as far as my understanding goes. I am using the sqlite3 module in python. Not sqlite or sql... But I could be mistaken. — sonfollower, Nov 11 '11 at 03:02

score 89 · Answer 1 · answered Nov 11 '11 at 02:50

89

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

answered Nov 11 '11 at 02:50

laher

8,860
3
29
39

4

D'oh, I just made this mistake. Thanks for catching the real problem: **omitting the left operand of the LIKE operator**. And thanks to the OP for thinning the embarrassment. – Bob Stein Mar 26 '13 at 17:43

score 26 · Accepted Answer · edited May 23 '17 at 10:31

26

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]';

edited May 23 '17 at 10:31

Community

1
1

answered Nov 11 '11 at 03:06

Michael Low

24,276
16
82
119

5

For clarification for users not very familiar with regular expressions, it could be something like 'table' NOT REGEXP 'word1|word2|word3|word4|word5'; etc – Graph Nov 26 '20 at 16:08

score 8 · Answer 3 · edited Oct 04 '16 at 20:49

8

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%'

edited Oct 04 '16 at 20:49

implmentor

1,386
4
21
32

answered Oct 04 '16 at 19:22

Etherlord

81
1
1

score 8 · Answer 4 · answered Feb 18 '19 at 11:16

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';

score 4 · Answer 5 · edited Apr 28 '16 at 14:51

4

The query you are after will be

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

edited Apr 28 '16 at 14:51

zx485

28,498
28
50
59

answered Apr 28 '16 at 14:29

David Hallam

41
2

Raymond Hettinger · Answer 6 · 2011-11-11T03:12:55.587

4

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.

edited Nov 11 '11 at 03:12

answered Nov 11 '11 at 03:07

Raymond Hettinger

216,523
63
388
485

3

He is probably avoiding the AND clause because it makes the query so verbose. There should be some feature in SQL to combine multiple values in a list *a la* `NOT IN`, that way we only have to write ` NOT LIKE` once and then the list of values to compare. This is a reasonable wish and it's surprising that SQL does not have such a feature for this condition. – TylerH Feb 07 '18 at 20:32
1

@TylerH It is difficult to talk about "SQL" in general for something like this. PostgreSQL, for example, has [`op any(array[...])` and `op all(array[...])`](https://www.postgresql.org/docs/current/static/functions-comparisons.html#idm46428706346880) constructs for this (with `in (list)` and `not in (list) being special cases of `=any` and `<> all`) , other SQL dialects do it differently. Similarly for regexes and the "standard" `similar to` pattern matching operator. – mu is too short Feb 07 '18 at 20:58

score 1 · Answer 7 · answered Jan 31 '15 at 15:13

1

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%'

answered Jan 31 '15 at 15:13

Dariusz

11
1

score 0 · Answer 8 · answered Sep 07 '21 at 17:44

0

Try out below query which worked for me.

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

answered Sep 07 '21 at 17:44

Damith Karunarathne

5
2

score 0 · Answer 9 · answered Mar 08 '22 at 17:37

Answer: Regular Expression

Regular Expression aka Regex (regexp) are so powerful and used by almost every popular programming language.
Using this you can find multiple patterns within few keystrokes instead of multiple AND, OR statement.

Simple RegExp for the above solution:

SELECT word FROM table WHERE word REGEXP '^[a-zA-Z]*[^abz][a-zA-Z]*$';

Here:

^ represent the starting of the text
[a-zA-Z] represent that any one character (upper/lower case) can take place
* represent that whatever is preceded can be repeated 0 or more times
[^abz] represent that, except these three characters any other character can take place - only single character
$ represent the ending of the text

Find a great example list here

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

9 Answers9

Answer: Regular Expression

Here:

Linked