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:

Share this page:
  • Twitter
  • Digg
  • Slashdot
  • del.icio.us
  • Google Bookmarks
  • DZone
  • LinkedIn
  • Reddit
  • Facebook
  • Print

25 comments

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