SQL Not Like语句不起作用

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?

有谁知道这有什么问题吗？

5 个解决方案

#1

If WPP.COMMENT contains NULL, the condition will not match.

如果WPP.COMMENT包含NULL，则条件将不匹配。

This query:

这个查询：

SELECT  1
WHERE   NULL NOT LIKE '%test%'

will return nothing.

什么都不会回报

On a NULL column, both LIKE and NOT LIKE against 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?

您能否发布您认为应退回的一行的相关值，但事实并非如此？

#2

Is the value of your particular COMMENT column null?

特定COMMENT列的值是否为null？

Sometimes NOT LIKE doesn't know how to behave properly around nulls.

有时候不喜欢不知道如何在空值周围正常行事。

#3

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”我正在评估的领域的另一面为我工作。

#4

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 ISNULL command, eg

因此，您可以使用ISNULL命令暂时替换null（空）记录中的值，例如

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%'

#5

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的其他答案也是一个好主意。

#1