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.
Best Answer
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]';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%'this is a select command
FROMuserWHEREapplication_key = 'dsfdsfdjsfdsf'AND email NOT LIKE '%applozic.com'AND email NOT LIKE '%gmail.com'AND email NOT LIKE '%kommunicate.io';this update command
UPDATE userSET email = nullWHERE application_key='dsfdsfdjsfdsf' and email not like '%applozic.com' and email not like '%gmail.com' and email not like '%kommunicate.io';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%'INTERSECTSELECT word FROM table WHERE word NOT LIKE '%b%'INTERSECTSELECT word FROM table WHERE word NOT LIKE '%c%';Alternatively, you can use a regular expression if your version of SQL supports it.
The query you are after will be
SELECT word FROM table WHERE word NOT LIKE '%a%' AND word NOT LIKE '%b%'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%'Try out below query which worked for me.
SELECT word FROM table WHERE NOT (word LIKE '%a%' AND word LIKE '%b%');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