empty string v.s. null

[elaine3839]

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

[jgmagnus]

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

[amar]

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

[chrisrlong]

They are definitely treated as NULLS.

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

- Chris

[jgmagnus]

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

[chrisrlong]

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