Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

sqlite

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.

like image 827
Elmer Avatar asked Nov 11 '11 02:11

Elmer


People also ask

How can I add multiple Not like in SQL?

So, here is the easiest solution. select * from table1 where column1 not like '%value1%' and column1 not like '%value2%' and column1 not like'%value3%'; If you want to play around with the Boolean logic, you rearrange the query like this.

Can we use multiple conditions in WHERE clause?

You can specify multiple conditions in a single WHERE clause to, say, retrieve rows based on the values in multiple columns. You can use the AND and OR operators to combine two or more conditions into a compound condition. AND, OR, and a third operator, NOT, are logical operators.

Can we use multiple columns in WHERE clause?

But the WHERE.. IN clause allows only 1 column.

How can use multiple WITH clause in SQL query?

To have multiple WITH clauses, you do not need to specify WITH multiple times. Rather, after the first WITH clause is completed, add a comma, then you can specify the next clause by starting with <query_name> followed by AS. There is no comma between the final WITH clause and the main SQL query.


2 Answers

SELECT word FROM table WHERE word NOT LIKE '%a%'  AND word NOT LIKE '%b%'  AND word NOT LIKE '%c%'; 
like image 84
laher Avatar answered Sep 30 '22 15:09

laher


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]'; 
like image 38
Michael Low Avatar answered Sep 30 '22 15:09

Michael Low