Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    4

    Question Unanswered: Empty String as Null???

    Why does Oracle treat empty strings as NULL values?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Empty String as Null???

    Originally posted by MES
    Why does Oracle treat empty strings as NULL values?
    It is one of Oracle's shortcomings and contrary to ANSI standards. The reason is to do with the way Oracle physically stores the data for strings, which is as a variable length string of characters and a character count. For the empty string, all it stores is the character count of zero. But Oracle also uses that character count to indicate whether a column is NULL (0) or not null (>0). So it cannot distinguish between '' and NULL.

    This is a historic mistake, which I guess Oracle either thinks is too difficult to fix, or not worth fixing.

  3. #3
    Join Date
    Jul 2003
    Location
    Near Paris France
    Posts
    60
    Oracle Help says :

    If a column in a row has no value, then the column is said to be null, or to contain a
    null. Nulls can appear in columns of any datatype that are not restricted by NOT
    NULL or PRIMARY KEY integrity constraints. Use a null when the actual value is not
    known or when a value would not be meaningful.
    Do not use null to represent a value of zero, because they are not equivalent. (Oracle
    currently treats a character value with a length of zero as null. However, this may
    not continue to be true in future releases, and Oracle recommends that you do not
    treat empty strings the same as nulls.) Any arithmetic expression containing a null
    always evaluates to null. For example, null added to 10 is null. In fact, all operators
    (except concatenation) return null when given a null operand.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by pmscontact
    Oracle Help says :
    ...
    (Oracle currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.) A
    That's great! And how does Oracle Help suggest you go about treating them differently when there is no way to distinguish between them within Oracle? I mean, this code doesn't even work in Oracle:

    SELECT * FROM emp
    WHERE middle_name = '';

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •