比较Oracle案例中的字符串何时

我有一个oracle案例的问题.

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

此查询始终返回null,但显然结果应该是第一种情况.我在Oracle中缺少关于字符串比较的东西吗？

你正在检查一个空字符串的字符串,因此有问题; 在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 

关于Oracle处理空字符串和null的方式,在这里您可以找到更多内容

一个例子:

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