Doubt on Strings of Zero Length in oracle

[oralearner]

Oracle 816 Documentation states:

"Strings of Zero Length Are Not Equivalent To a NULL
A string of zero length ('') is not equivalent to a NULL.

According to the ANSI SQL 1992 Transitional standard, a zero-length or empty string is not the same as NULL. The Oracle database server may comply fully with this aspect of the standard in the future. "


select length('') from dual;

LENGTH('')
----------

select length(null) from dual;


LENGTH(NULL)
------------


select 1 from dual where '' is null;


1
----------
1

Does this mean that '' is null ?

 

[dbtoo2001]

what do you get for the following:

select 1 from dual where ''='';
and
select nvl('','Y') from dual;

 

[SantaMufasa]

Ora,

Stepping back from the forest, a NULL, by conceptual definition is "an unknown value". Is "an unknown value" equal to "an unknown value"? We think not; in fact, the answer itself is "unknown" (but certainly not TRUE). Is "an unknown value" an "unknown value"? We think so. Can you check "EQUALITY" (i.e., bit match) "an unknown value" (no bits) against the bits of anything else (whether the "anything else" has bits or no bits? Yes, you can check for EQUALITY, but the answer will always be "UNKNOWN" (but certainly not TRUE or FALSE).

Can we, however, check an item to determine whether or not its content "has bits" (i.e., IS NULL or IS NOT NULL)? We think so.

So, in the final analysis, an EQUALITY check (bit check) against anything that is NULL will always respond with an answer of "UNKNOWN" (but never TRUE or FALSE). The only conditional check we can do against a NULL (unknown value) that will result in a definite TRUE or FALSE is to ask "...IS NOT NULL" or "...IS NULL", which checks whether or not the data length of the comparand is 'zero'.

Regardless of ISO, this is the current (and past) mindset of every available version of Oracle.

Cheers,

Dave
Sandy, Utah, USA @ 17:17 (21Nov03) GMT, 10:17 (21Nov03) Mountain Time

 

[ddrillich]

oralearner,

Please have a look at:
thread759-657480