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.


Be Sociable, Share!


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

  1. 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!

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

  3. can you give more examples

Leave a comment