比较Oracle情况下的字符串 [英] Compare String in Oracle Case When

问题描述

我在什么时候遇到oracle大小写问题.

i have an issue with oracle case when.

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

此查询始终返回null，尽管很明显结果应该是第一种情况.我是否缺少有关oracle中字符串比较的内容?

this query always returns null, though it's obvious the result should be the first case. Am i missing something about string comparison in oracle?

推荐答案

您正在检查字符串还是一个空字符串，因此出现了问题；在Oracle中，最好检查一下字符串is not null:

You are checking strings againts an empty string, thus having issues; in Oracle you'd better check if your string 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的方式，在这里，您会发现更多

About the way Oracle treats empty string and null, here you find something more

一个例子:

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

In a word, the only check you can rely on, when talking about NULL, is: IS [NOT] NULL

这篇关于比较Oracle情况下的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！