SQL Not Like 语句不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1638566/
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
SQL Not Like Statement not working
提问by mattgcon
I have the following code within a stored procedure.
我在存储过程中有以下代码。
WHERE
WPP.ACCEPTED = 1 AND
WPI.EMAIL LIKE '%@MATH.UCLA.EDU%' AND
(WPP.SPEAKER = 0 OR
WPP.SPEAKER IS NULL) AND
WPP.COMMENT NOT LIKE '%CORE%' AND
WPP.PROGRAMCODE = 'cmaws3'
The NOT LIKE statement is not working, and yes before anyone says anything there are items with the COMMENT column that does not include CORE and all the other columns are ok.
NOT LIKE 语句不起作用，是的，在任何人说任何事情之前，COMMENT 列中的项目不包括 CORE，所有其他列都可以。
Does anyone know what is wrong with this?
有谁知道这有什么问题？
回答by Quassnoi
If WPP.COMMENTcontains NULL, the condition will not match.
如果WPP.COMMENT包含NULL，条件将不匹配。
This query:
这个查询：
SELECT 1
WHERE NULL NOT LIKE '%test%'
will return nothing.
不会返回任何东西。
On a NULLcolumn, both LIKEand NOT LIKEagainst any search string will return NULL.
在一NULL列，两者LIKE并NOT LIKE反对任何搜索字符串将返回NULL。
Could you please post relevant values of a row which in your opinion should be returned but it isn't?
您能否发布您认为应该返回但事实并非如此的行的相关值？
回答by Paul D. Starbrook
Just come across this, the answer is simple, use ISNULL. SQL won't return rows if the field you are testing has no value (in some of the records) when doing a text comparison search, eg:
刚遇到这个，答案很简单，使用ISNULL. 如果在进行文本比较搜索时您正在测试的字段没有值（在某些记录中），则 SQL 不会返回行，例如：
WHERE wpp.comment NOT LIKE '%CORE%'
So, you have temporarily substitute a value in the null(empty) records by using the ISNULLcommand, eg
因此，您null使用ISNULL命令临时替换了（空）记录中的值，例如
WHERE (ISNULL(wpp.comment,'')) NOT LIKE '%CORE%'
This will then show all your records that have nulls and omit any that have your matching criteria. If you wanted, you could put something in the commas to help you remember, eg
这将显示所有具有空值的记录，并省略任何具有匹配条件的记录。如果你愿意，你可以在逗号中放一些东西来帮助你记住，例如
WHERE (ISNULL(wpp.comment,'some_records_have_no_value')) NOT LIKE '%CORE%'
回答by James Cronen
Is the value of your particular COMMENT column null?
您的特定 COMMENT 列的值是否为空？
Sometimes NOT LIKE doesn't know how to behave properly around nulls.
有时 NOT LIKE 不知道如何正确处理空值。
回答by Valiante
I just came across the same issue, and solved it, but not before I found this post. And seeing as your question wasn't really answered, here's my solution (which will hopefully work for you, or anyone else searching for the same thing I did;
我刚刚遇到了同样的问题，并解决了它，但不是在我找到这篇文章之前。看到你的问题没有得到真正的回答，这是我的解决方案（希望它对你有用，或者其他任何人都在寻找我所做的同样的事情；
Instead of;
代替;
... AND WPP.COMMENT NOT LIKE '%CORE%' ...
Try;
尝试;
... AND NOT WPP.COMMENT LIKE '%CORE%' ...
Basically moving the "NOT" the other side of the field I was evaluating worked for me.
基本上将“NOT”移到我正在评估的领域的另一边对我有用。
回答by Mark Kadlec
mattgcon,
mattgcon，
Should work, do you get more rows if you run the same SQL with the "NOT LIKE" line commented out? If not, check the data. I know you mentioned in your question, but check that the actual SQL statement is using that clause. The other answers with NULL are also a good idea.
应该工作，如果您运行相同的 SQL 并注释掉“NOT LIKE”行，您会得到更多行吗？如果没有，请检查数据。我知道您在问题中提到过，但请检查实际的 SQL 语句是否使用了该子句。NULL 的其他答案也是一个好主意。
