如何在Oracle中的NOT IN和NOT LIKE语句中处理空值? [英] How do I handle nulls in NOT IN and NOT LIKE statements in Oracle?

问题描述

我有很长的PL/SQL，除了where子句的一小部分之外，它们都能正常工作.

I have a long piece of PL/SQL which is working well except for one small part of the where clause.

我希望应该返回一行，但是，由于where子句检查的字段为null，因此即使符合条件也不会返回该行.

I'm expecting that one row should be returned but, because the fields the where clause checks are null, the row is not being returned despite meeting the criteria.

我在这里读了一个很好的笔记:
http://jonathanlewis.wordpress.com/2007/02/25/not-在/

I read a very good note here :
http://jonathanlewis.wordpress.com/2007/02/25/not-in/

它说明了空值如何影响NOT IN语句，并且我认为对于NOT LIKE同样如此.

It explains how Nulls affect NOT IN statements and I presume it's the same for NOT LIKE as well.

我找不到的是我需要的类似替代品.我是否需要以某种方式将其切换为IN和LIKE或提供某些内容以在NUll情况下返回该行?

What I haven't been able to find is the comparable replacement I need. Do I need to somehow switch this into an IN and LIKE or provide something to return the row if NUll?

这是我的代码的简化版本.

Here is a simplified version of my code.

SELECT * FROM Temp_Table T
WHERE -- Other where constraints
AND (T.Col_One NOT LIKE 'AString'
     OR T.Col_Two NOT IN ('BString','CString'))

在我的情况下，该行在Col_One和Col_Two中都为空.

In my situation the row would have nulls in both Col_One and Col_Two.

非常感谢您的帮助.

谢谢.

推荐答案

尝试一下:

AND (NVL(T.Col_One,'NuLl') NOT LIKE 'AString'
      OR NVL(T.Col_Two,'NuLl') NOT IN ('BString','CString')) 

这篇关于如何在Oracle中的NOT IN和NOT LIKE语句中处理空值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！