Compare String in Oracle Case When

Question

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

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?

Are you sure you are using this query and/or you are reading the output correctly? — Tim Biegeleisen, Jan 20 '17 at 07:29
yes, the output is null. this query actually is from an debugged insert query, it always inserts null value, i am curious why, so i cut it and exe it on select query, it returns null — deckamaha, Jan 20 '17 at 07:33

score 9 · Accepted Answer · edited May 23 '17 at 10:29

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

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

An example:

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

gives:

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

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

Interesting, but is there a reason why the semantics of empty string should be different than a non-empty string? — Tim Biegeleisen, Jan 20 '17 at 07:32
@TimBiegeleisen: Great Oracle non sense! So Aleksej is right, so upvote — Joe Taras, Jan 20 '17 at 07:36
@TimBiegeleisen: to be honest, I have no idea at all :). I'll simply cite Justin Cave: "I believe the answer is that Oracle is very, very old." — Aleksej, Jan 20 '17 at 07:40

score 4 · Answer 2 · answered Jan 20 '17 at 07:38

Well, the reason of such a behaviour is that Oracle doesn't have empty string, but null; that's why

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

is actually

   select case when 'abc' != null

and since anything != null is null (true, false, null boolean logic) all the when don't return true and else is executed. The right syntax is

   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

Compare String in Oracle Case When

2 Answers2

Linked