Re: VARCHAR2: NULL value vs. empty string

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1071424889.658804_at_yasure>...
> Daniel Gustafsson wrote:
>
> > From the standard compliance tables in the Oracle SQL Reference
> > manual.
> > "Oracle partially supports these subfeatures:
> > E021-02, CHARACTER VARYING data type (Oracle does not distinguish a
> > zero-length VARCHAR string from NULL)
> > E021-03, Character literals (Oracle regards the zero-length literal ''
> > as being null)"
>
> And how, exactly, do you correlate this with the ANSI standard? NULL in
> the standard is a lack of a value ... it is not a zero length string.
>
> You are making an assumption. And an incorrect one.

Daniel,

You do seem (unusually) to be somewhat confused. The relevant ANSI standard text about the empty string ('') not being NULL, and Oracle's admission of non-compliance on that point, have been quoted several times. You state here that NULL is not a zero length string - correct. OK, so how do you represent a zero length string in Oracle? Answer: you have to use a NULL.

Nobody is arguing (as you seem to have believed) that Oracle's treatment of NULLs and 3-valued logic is wrong. It is Oracle's treatment (or lack of treatment) of the empty string that is "wrong", or at least non-ANSI. Because of the way Oracle implements NULLs, it has no way to distinguish between '' and NULL - both are represented by a character count of zero.

If NULL is not a zero length string, then why does Oracle do this? :-

SQL> select 'abc'||null from dual;

'AB

---
abc

It should return NULL, should it not?