An empty string is not equal to an empty string? WTF?



  • Soo, I need to import a text file which contains a date column that does not always contain a value. Obviously, just doing to_date() will fail when said column is empty. So I tried a case statement to limit to_date() calls to those cases when that column was filled.

    Just, I could not get it to work. So I tried to simplify that CASE statement and boiled it down to:

    <FONT size=1>

    select CASE WHEN </FONT><FONT color=#0000f0 size=1>''</FONT><FONT size=1> = </FONT><FONT color=#0000f0 size=1>''</FONT><FONT size=1> THEN </FONT><FONT color=#0000f0 size=1>'equal'</FONT><FONT size=1> ELSE </FONT><FONT color=#0000f0 size=1>'not equal'</FONT><FONT size=1> END FROM dual</FONT>

    <FONT size=1>The result? Not equal. So I tried</FONT>

    <FONT size=1>

    select CASE WHEN </FONT><FONT color=#0000f0 size=1>''</FONT><FONT size=1> is null THEN </FONT><FONT color=#0000f0 size=1>'equal'</FONT><FONT size=1> ELSE </FONT><FONT color=#0000f0 size=1>'not equal'</FONT><FONT size=1> END FROM dual

    </FONT><FONT size=1>This results in 'equal'.</FONT>

    <FONT size=1>WTF? I know that the empty string is null, but why doesn't Oracle convert both empty strings to null, so they are equal? Grmpf.</FONT>



  • It does convert both strings to null.

    It's a basic fundamental principle that NULL is neither equal to NULL, nor it is not equal to NULL.

    Null is an unknown. You can't compare an unknown with another unknown. This is like saying "I'm thinking of a number, you're thinking of a number. Neither of us know what both those numbers are. Now ... tell me if they're the same, or different."

     



  • Not sure if a work-around is being requested, but...

    select CASE WHEN NVL(<FONT color=#0000f0 size=1>'', '<NULL>')</FONT><FONT size=1> = NVL(</FONT><FONT color=#0000f0 size=1>'', '<NULL>')</FONT><FONT size=1> THEN </FONT><FONT color=#0000f0 size=1>'equal'</FONT><FONT size=1> ELSE </FONT><FONT color=#0000f0 size=1>'not equal'</FONT><FONT size=1> END FROM dual</FONT>

    should do it...  (last used oracle 5+ years ago...)



  • @RaspenJho said:

    Not sure if a work-around is being requested, but...

    select CASE WHEN NVL(<FONT color=#0000f0 size=1>'', '<NULL>')</FONT><FONT size=1> = NVL(</FONT><FONT color=#0000f0 size=1>'', '<NULL>')</FONT><FONT size=1> THEN </FONT><FONT color=#0000f0 size=1>'equal'</FONT><FONT size=1> ELSE </FONT><FONT color=#0000f0 size=1>'not equal'</FONT><FONT size=1> END FROM dual</FONT>

    should do it...  (last used oracle 5+ years ago...)

    Unless the file really contains the literal '<NULL>'...

    Am I the only one to find this awkward to use and counter-intuitive? If Oracle secretly converts empty strings to null, why doesn't it convert the "=" to "IS" while it's at it?



  • 1) There is no 'secretly' about it. Oracle documentation states it plainly : An empty string evaluates to null.

    2) Why should it convert to IS? You should be using IS in the first place. 



  • Okay, another example: Let's say you have a table with 3 columns, two strings and number. Now you want to display all the numbers, with the added requirement that the number should be negated iff the two string columns match. The string columns are allowed to be empty and if they both are, that counts as a match (note that in most cases you would omit the last sentence from the requirements, because it is commonly agreed upon that two empty strings are equal).

    Intuitive approach: [code]SELECT number * (CASE WHEN string1 = string2 THEN -1.0 ELSE 1.0 END) FROM table[/code]

    Using Oracle, this would return incorrect results when string1 and string2 both are empty. You'd have to write something like [code]SELECT number * (CASE WHEN string1 = string2 OR (string1 IS NULL AND string2 IS NULL) THEN -1.0 ELSE 1.0 END) FROM table[/code]

    Three conditions instead of one. Do you get used to this once you have worked with Oracle for a couple of years?



  • @Skurry said:

    Okay, another example: Let's say you have a table with 3 columns, two strings and number. Now you want to display all the numbers, with the added requirement that the number should be negated iff the two string columns match. The string columns are allowed to be empty and if they both are, that counts as a match (note that in most cases you would omit the last sentence from the requirements, because it is commonly agreed upon that two empty strings are equal).

    Intuitive approach: [code]SELECT number * (CASE WHEN string1 = string2 THEN -1.0 ELSE 1.0 END) FROM table[/code]

    Using Oracle, this would return incorrect results when string1 and string2 both are empty. You'd have to write something like [code]SELECT number * (CASE WHEN string1 = string2 OR (string1 IS NULL AND string2 IS NULL) THEN -1.0 ELSE 1.0 END) FROM table[/code]

    Three conditions instead of one. Do you get used to this once you have worked with Oracle for a couple of years?

    select number * (case when string1 <> string2 then 1 else -1 end) from table;

    should do the trick 



  • Can you do this?:

    SELECT number * ( CASE WHEN string1 = string2 OR string1 IS string2) THEN -1.0 ELSE 1.0 END ) FROM table

     



  • @RaspenJho said:

    Can you do this?:

    SELECT number * ( CASE WHEN string1 = string2 OR string1 IS string2) THEN -1.0 ELSE 1.0 END ) FROM table

     

    at least not in Oracle; the keyword IS requires the keyword NULL



  • My personal favourite:

     

    x = ''

    y = x

     

    SELECT CASE WHEN y = x THEN 'As it should be' ELSE 'Lame, Oracle, lame'




  • @woodle said:

    My personal favourite:

     

    x = ''

    y = x

     

    SELECT CASE WHEN y = x THEN 'As it should be' ELSE 'Lame, Oracle, lame'


    What kind of code is that?, SQL Server?

    x := '';
    y := x;
    SELECT CASE
    WHEN y = x THEN 'woodle is smart'
    WHEN y is null THEN 'woodle doesn''t get it'
    ELSE 'wtf?' END
    INTO result
    FROM dual;


  • I think it was PseudoCode.Net™



  • @nerdydeeds said:

    What kind of code is that?, SQL Server?

    x := '';
    y := x;
    SELECT CASE
    WHEN y = x THEN 'woodle is smart'
    WHEN y is null THEN 'woodle doesn''t get it'
    ELSE 'wtf?' END
    INTO result
    FROM dual;

    That's very pretty, although I think you might have meant

    ELSE File_Not_Found END

    which is, naturally, what you would want in the highly unusual case where y is not null and not equal to x.

    The point, which you apparently missed while you were concentrating on 'i' dotting and 't' crossing, is that if you assign the value of one variable to another they can immediately be not equal to each other.

    As an exercise you might try to think about the case where x isn't explicitly set to a zero-length string as in the example but where it might the result of some string manipulations where you started out with a non-zero length string, but somewhere along the way your string was magically transformed into something else.  Now imagine if that could happen in Java or C#.

    "Elegant, sturdy and bursting with class."



  • @Skurry said:

    Soo, I need to import a text file which contains a date column that does not always contain a value. Obviously, just doing to_date() will fail when said column is empty. So I tried a case statement to limit to_date() calls to those cases when that column was filled.

    Just, I could not get it to work. So I tried to simplify that CASE statement and boiled it down to:

    <font size="1"> </font>

    <font size="1">select</font><font size="1"> CASE WHEN </font><font color="#0000f0" size="1">''</font><font size="1"> = </font><font color="#0000f0" size="1">''</font><font size="1"> THEN </font><font color="#0000f0" size="1">'equal'</font><font size="1"> ELSE </font><font color="#0000f0" size="1">'not equal'</font><font size="1"> END FROM dual</font>

    <font size="1">The result? Not equal. So I tried</font>

    <font size="1"> </font>

    <font size="1">select</font><font size="1"> CASE WHEN </font><font color="#0000f0" size="1">''</font><font size="1"> is null THEN </font><font color="#0000f0" size="1">'equal'</font><font size="1"> ELSE </font><font color="#0000f0" size="1">'not equal'</font><font size="1"> END FROM dual</font>

    <font size="1">This results in 'equal'.</font>

    <font size="1">WTF? I know that the empty string is null, but why doesn't Oracle convert both empty strings to null, so they are equal? Grmpf.</font>

    I think that in this case, whomever wrote DECODE had this in mind.

     

    Try this,

     

    select decode('','','equal','not equal') FROM dual

     

    It does properly compare two null values 


Log in to reply