SQL 如何在 sqlite3 的 WHERE 子句中添加多个“NOT LIKE '%?%'？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8089096/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I add multiple "NOT LIKE '%?%' in the WHERE clause of sqlite3?
提问by Elmer
I have a sqlite3 query like:
我有一个 sqlite3 查询，如：
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.
这将选择单词中不出现“a”的所有单词。这我可以完美地工作。问题是我是否想进一步限制结果，使其不包含单词中的任何地方的“b”。我正在想象这样的事情。
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 ANDclause is what I'm trying to avoid:
这显然行不通，但这就是想法。只是添加一个AND条款是我试图避免的：
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.
如果这是唯一的选择，那么我将不得不使用它，但我希望有其他选择。
采纳答案by mikel
If you use Sqlite's REGEXP support ( see the answer at Problem with regexp python and sqlitefor how to do that ) , then you can do it easily in one clause:
如果您使用 Sqlite 的 REGEXP 支持（请参阅问题与 regexp python 和 sqlite 中的答案以了解如何执行此操作），那么您可以在一个子句中轻松完成：
SELECT word FROM table WHERE word NOT REGEXP '[abc]';
回答by laher
SELECT word FROM table WHERE word NOT LIKE '%a%'
AND word NOT LIKE '%b%'
AND word NOT LIKE '%c%';
回答by Etherlord
You missed the second statement: 1) NOT LIKE A, AND 2) NOT LIKE B
你错过了第二个陈述：1）不喜欢 A，和 2）不喜欢 B
SELECT word FROM table WHERE word NOT LIKE '%a%' AND word NOT LIKE '%b%'
回答by Vipin Pandey
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';
回答by David Hallam
The query you are after will be
您所追求的查询将是
SELECT word FROM table WHERE word NOT LIKE '%a%' AND word NOT LIKE '%b%'
回答by Raymond Hettinger
I'm not sure why you're avoiding the ANDclause. It is the simplest solution.
我不确定您为什么要避免使用AND子句。这是最简单的解决方案。
Otherwise, you would need to do an INTERSECTof multiple queries:
否则，您需要对多个查询进行INTERSECT：
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.
或者，如果您的 SQL 版本支持，您可以使用正则表达式。
回答by Dariusz
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%'
