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.
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.
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.
But the WHERE.. IN clause allows only 1 column.
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.
SELECT word FROM table WHERE word NOT LIKE '%a%' AND word NOT LIKE '%b%' AND word NOT LIKE '%c%';
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]';
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With