比较Oracle情况下的字符串时

Question

我对甲骨文有异议。

SELECT CASE WHEN '7C54D3E133830A78E040A8C010014B7D' != ''
            THEN '7C54D3E133830A78E040A8C010014B7D'
            WHEN 'e84a4433966c4b8996ce34905acff63d' != ''
            THEN 'e84a4433966c4b8996ce34905acff63d'
            WHEN '7faa9126b1c6412fa58375ab2b2be1db' != ''
            THEN '7faa9126b1c6412fa58375ab2b2be1db'
            ELSE NULL
 END
 FROM DUAL

这个查询总是返回null，不过很明显，结果应该是第一个情况。我是不是遗漏了甲骨文中的字符串比较？

Answer 1

您正在检查空字符串的字符串，因此有问题；在Oracle中，最好检查字符串is not null

SELECT CASE WHEN '7C54D3E133830A78E040A8C010014B7D' is not null
            THEN '7C54D3E133830A78E040A8C010014B7D'
            WHEN 'e84a4433966c4b8996ce34905acff63d' is not null
            THEN 'e84a4433966c4b8996ce34905acff63d'
            WHEN '7faa9126b1c6412fa58375ab2b2be1db' is not null
            THEN '7faa9126b1c6412fa58375ab2b2be1db'
            ELSE NULL
 END
 FROM DUAL 

关于甲骨文对待空字符串和空字符串的方式，here可以找到更多的东西

举个例子：

select q'['' = '']'         , case when '' = ''            then 'YES' else 'NO' end from dual union all
select q'['' is null]'      , case when '' is null         then 'YES' else 'NO' end from dual union all 
select q'['' = null ]'      , case when '' = null          then 'YES' else 'NO' end from dual union all 
select q'[null = null]'     , case when null = null        then 'YES' else 'NO' end from dual union all 
select q'[null is null]'    , case when null is null       then 'YES' else 'NO' end from dual union all 
select q'['' != '']'        , case when '' != ''           then 'YES' else 'NO' end from dual union all
select q'['' is not null]'  , case when '' is not null     then 'YES' else 'NO' end from dual union all
select q'['' != null ]'     , case when '' != null         then 'YES' else 'NO' end from dual union all
select q'[null != null]'    , case when null != null       then 'YES' else 'NO' end from dual union all
select q'[null is not null]', case when null is not null   then 'YES' else 'NO' end from dual

给予：

'' = ''           NO
'' is null        YES
'' = null         NO
null = null       NO
null is null      YES
'' != ''          NO
'' is not null    NO
'' != null        NO
null != null      NO
null is not null  NO

总之，在谈到NULL时，唯一可以依赖的检查是：IS [NOT] NULL

Answer 2

那么，这种行为的原因是甲骨文没有空字符串，而是null；这就是为什么

   select case when 'abc' != ''  
      ....

实际上是

   select case when 'abc' != null

因为anything != null是null (true，false，null布尔逻辑)，所以when不返回true，else也被执行。正确的语法是

   SELECT CASE WHEN '7C54D3E133830A78E040A8C010014B7D' IS NOT NULL
               THEN '7C54D3E133830A78E040A8C010014B7D'
               WHEN 'e84a4433966c4b8996ce34905acff63d' IS NOT NULL
               THEN 'e84a4433966c4b8996ce34905acff63d'
               WHEN '7faa9126b1c6412fa58375ab2b2be1db' IS NOT NULL
               THEN '7faa9126b1c6412fa58375ab2b2be1db'
               ELSE NULL
          END
     FROM DUAL