How do I add multiple "NOT LIKE '%?%' in the WHERE clause of sqlite3?
SqlSqliteSql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
SELECT word FROM table WHERE word NOT LIKE '%a%'
AND word NOT LIKE '%b%'
AND word NOT LIKE '%c%';
Solution 2 - Sql
If you use Sqlite's REGEXP support ( see the answer at https://stackoverflow.com/questions/5365451/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]';
Solution 3 - Sql
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%'
Solution 4 - Sql
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';
Solution 5 - Sql
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.
Solution 6 - Sql
The query you are after will be
SELECT word FROM table WHERE word NOT LIKE '%a%' AND word NOT LIKE '%b%'
Solution 7 - Sql
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%'
Solution 8 - Sql
Try out below query which worked for me.
SELECT word FROM table WHERE NOT (word LIKE '%a%' AND word LIKE '%b%');
Solution 9 - Sql
> ## 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:
>lang=html >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