Oracle empty string = null

Saturday, 26 April 2003

In Oracle 8, there is no such thing as a zero-length string. Any zero-length string, either from a function call or the literal '', is treated as null.

Some of the consequences of this are counterintuitive. Here are some examples.

  • The expression X='' is never true, regardless of what X is. In particular:
  • The expression ''='' is not true.
  • If you store an empty string ('') and read it back, the result will be null.
  • The expression trim(' ') returns null.

Be careful of this when writing your queries, particularly if you are porting code between Oracle and another database.

Tags:

25 comments

You can leave a comment, or trackback from your own site.

  1. In the example above, the meaning of ‘1’ is “a range covering all phone numbers starting with ‘1’ “, and the meaning of ” is “a range covering all phone numbers starting with ” “, that is all possible numbers in the world. The reason for this concept is to calculate call rates depending on phone dialed. The empty string is used for all countries not included in the table, in case we didn’t bother placing a price for a given country, or worse still if we failed to include some country. A null value would have a different meaning – “skip this row, it’s INVALID”, while an empty string is perfectly valid and meaningfull.

    Away from the specific interpretation, a string is the computer/lexical equivalent of the mathematical concept of tuple (ordered sequence). A 2-character string is the equivalent of 2-tuple. A 1-character string is the equivalent of 1-tuple. Well not really a string – it has just one character? Still, nobody rejects the concept of 1-character strings. Neither does mathematics. A 0-character (empty) string is the equivalent of 0-tuple – a completely valid mathematical object.

    Another similar concept (both in IT/life and maths) is the concept of sets. The difference between a set and a tuple is that the set has no specific order of the elements. The similarity is that the set can have 0 elements too. It’s a completely valid set, as is the 0-tuple and the empty string. Any operation specific to the object may by applied to the empty object, as it is comlpetely valid and meaningfull. In contrast, the NULL value has no meaning and is invalid by definition.

  2. One big problem with NULL==” is that it totally breaks the closure rules for normal string operations. You suddenly can’t be sure that e.g. a trim() or a substr() taken from a non-null is still non-null. That is a real pain if you try to do complex string processing.

  3. Hi,

    I’m trying to write a query that can run both on SQLSERVER and ORACLE.
    For example, if one table have a field with null and ”, and I need only lines that have values, how can I do that?
    IS NOT NULL works with oracle but dont work with sqlserver (the blank line is in the result)
    If I put IS NOT NULL AND ” oracle brigs no lines…
    I’m stuck!

    Thanx for any help!

  4. @Fabio, you could try checking the length. Something like
    select * from tablename where 0 < char_length(columnname)

  5. can you give more examples

Leave a comment