empty string v.s. null
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: empty string v.s. null

  1. #1
    Join Date
    May 2001
    Posts
    285

    Exclamation

    Hi,

    As far as I know, if I define a column as varchar2(or other char data type) in Oracle and insert an empty string to that column, Oracle will treat this column the same as null. This implementation is NOT the same as sql server, which can differentiate an empty string and a null value.

    So is this a configurable feature? i.e. can we change some settings so Oracle can also tell the difference between an empty string and null?

    Any workaround on it?

    Thanks,
    Elaine

  2. #2
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Talking my test says yer wrong.


    look at the following then tell me if it proves you wrong. If not I'll try something else:

    dbdev:staging> select count(*) from test where string IS NULL;

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

    dbdev:staging> insert into test values (' ');

    1 row created.

    dbdev:staging> commit;

    Commit complete.

    dbdev:staging> select count(*) from test where string IS NULL;

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


    - magnus

  3. #3
    Join Date
    Mar 2001
    Posts
    314
    Perform the following test to find out

    create table tt ( c1 varchar2(10) not null);

    insert into tt values ('') -- empty string no space betn 's

    If you get an error saying that can't insert NULL into tt then you know they are treated similarly or else...........


    -amar

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    They are definitely treated as NULLS.

    As for whether or not you can change this functionality... not that I know of.

    - Chris

  5. #5
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Unhappy


    i guess i am confused between an empty string and spaces. I thought they were the same.

    so, why would you want to know the difference between an empty string and NULL? don't they both signify 'no value'?

    - magnus

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    No. This is where SQLServer has it right an Oracle does not.

    Take for example, LastName. In the case of my neighbor Randy, I don't know his last name. Therefore, a value of NULL signifies that fact; an unknown value. On the other hand, Cher doesn't have a last name. An empty string would convey that fact; no data exists. However, the fact that Oracle converts an empty string to a NULL perverts the meaning of NULL at worst, or eliminates the ability to specify a value that is known to not exist at best.

    Basically, there is definitely a difference between NULL and empty, or a value that is not known vs. one that is known to not exist. I completely agree that these values should be treated differently, but they are not. And as I said before, I don't know of any way to change this functionality.

    - Chris

Posting Permissions

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


Click Here to Expand Forum to Full Width