Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171

    Unanswered: Empty and NULL values in oracle

    Hi,

    Was trying to study the NULL and EMPTy values in oracle. The following piece of code confused me. Can you throw some light so as to make my understanding better:

    PHP Code:

    SQL
    create table a (col1 varchar2(10));

    Table created.

    SQLinsert into a values ('');

    1 row created.

    SQLinsert into a values (null);

    1 row created.

    SQLselect count(*) from a where col1 '';

      
    COUNT(*)
    ----------
             
    0

    SQL
    select count(*) from a where col1 is null;

      
    COUNT(*)
    ----------
             
    2

    SQL
    select decode(col1null'its null','its empty'from a;

    DECODE(CO
    ---------
    its null
    its null

    SQL
    select decode(col1'' 'its empty' 'its null'from a;

    DECODE(CO
    ---------
    its empty
    its empty

    SQLselect decode(col1'' 'its empty' null'its null'' nothing'from a;

    DECODE(CO
    ---------
    its empty
    its empty

    SQL>

    SQL
    Oracle can do wonders !

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Empty string in Oracle is null. So you cant do comparisons against '' (i.e. = '') , you have to do 'is null'.

    Your decodes are just confusing the issue as decode will stop when it hits its first match.

    Alan

  3. #3
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Ok. Let me post the que explicitly:

    1. when the comparison of col is done in where clause, the 2 rows get selected when compared with null and not with ''

    2. whereas when comparison is done using decode, both qualify true for '' as well as null values.
    Oracle can do wonders !

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

    Re: Empty and NULL values in oracle

    Yes, this is one of the cases where Oracle doesn't "do wonders" so well (to quote your signature line)! In fact, you could say it is seriously flawed in this regard.

    Oracle is unable to distinguish between an empty or zero-length string and a NULL. Internally, they are both represented by a zero representing the string length and nothing else.

    Depending on what you do with it, Oracle treats a NULL character string as '' or NULL, as it deems fit:
    • in comparisions like x = '' it treats '' like a NULL, and so the comparison never yields TRUE or FALSE.
    • in concatenations it treats NULL like a zero-length string, so 'ABC'||NULL = 'ABC' not NULL
    • in the LENGTH function it treats '' as NULL, so LENGTH('') returns NULL not 0.


    In fact, I think it consistently treats '' as a NULL with the exception of concatenation!

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by cmasharma
    Ok. Let me post the que explicitly:

    1. when the comparison of col is done in where clause, the 2 rows get selected when compared with null and not with ''

    2. whereas when comparison is done using decode, both qualify true for '' as well as null values.
    In the DECODE, it is matched with whichever comes first '' or NULL, since they are considered to be the same!

  6. #6
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171

    Re: Empty and NULL values in oracle

    Thanks once again !
    This time with a new signature


    Originally posted by andrewst
    Yes, this is one of the cases where Oracle doesn't "do wonders" so well (to quote your signature line)! In fact, you could say it is seriously flawed in this regard.

    Oracle is unable to distinguish between an empty or zero-length string and a NULL. Internally, they are both represented by a zero representing the string length and nothing else.

    Depending on what you do with it, Oracle treats a NULL character string as '' or NULL, as it deems fit:
    • in comparisions like x = '' it treats '' like a NULL, and so the comparison never yields TRUE or FALSE.
    • in concatenations it treats NULL like a zero-length string, so 'ABC'||NULL = 'ABC' not NULL
    • in the LENGTH function it treats '' as NULL, so LENGTH('') returns NULL not 0.


    In fact, I think it consistently treats '' as a NULL with the exception of concatenation!
    Oracle can do wonders !

Posting Permissions

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