Oracle empty string = null

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.

26 April 2003 | By Bennett | Topics: Risks, SQL
Share This | Comments | Permalink | Trackback | Comments feed

11 responses

  • It’s a really bad behavior :( In MySQL empty string is not equal to null.

    Dead Krolik | 13 July 2007
  • Trackback | Aaron Johnson » Blog Archive » Fun with Supporting Multiple Databases | 25 September 2007
    [...] McElwee blogged about this feature in more detail on his blog, but the jist of this nugget of goodness is that if you attempt to [...]

  • That really should be considered as a Oracle design bug. Oracle really is not that good in my opinion.

    jianwu_chen | 23 October 2007

  • I am not at all an Oracle expert, but as a developer I have found Oracle databases to be annoying to work with. I think their selling point is the boring enterprise stuff like reliability, scalability, etc.

    Bennett | 23 October 2007

  • I personally like the fact that Oracle treats empty strings as null. Its a real pain having to test if something is null OR an empty string to find out if there is a usuable value there or not. And if I want to select rows where certain fields have blank values, I know that I will get everything I need by testing if null.

    Michael | 6 November 2007

  • Programming without null is like Mathematics without zero.

    bachew | 2 January 2008

  • I agree, ORACLE storing empty strings as NULL was a horrible design blunder. In a relational database, NULL is used to represent datum whose value is “unknown”. If I insert an empty string into a database field, I have specified a value for that field — a zero-length string. If I fetch that string from the database, the only correct result is an empty string.

    John | 17 January 2008

  • Oops, correction:
    A string variable that can’t be set empty is like a number variable that can’t be set zero.

    bachew | 22 January 2008

  • bachew - I agree.
    Also, you came back 20 days later to fix that. Awesome.

    Todd | 21 February 2008

  • @Michael: if you don’t want to test for both ” and null, and your application treats both as semantically the same, just declare the field ‘not null’

    (another) John | 29 February 2008
  • Trackback | executive orc house / I Hate It When You Make Me Do This | 8 May 2008
    [...] the semantic distinction between a value and not a value. Add the fact that the empty string  doesn’t exist in Larry’s World to th[...]

Post a comment

(required)
(required)
Close
E-mail It