Re: Oracle NULL vs '' revisited

On Aug 20, 1:40 pm, David Portas
<REMOVE_BEFORE_REPLYING_dpor..._at_acm.org> wrote:
> On 20 Aug, 10:58, William Robertson <williamr2..._at_googlemail.com>
> wrote:
>
> > I think however the above definition oversimplifies things by not
> > mentioning nulls.
>
> The SQL standard runs to more than 2000 pages and I think it has
> rarely been accused of oversimplifying things! Nulls are defined
> elsewhere.
>
> > In the case where a missing value stands for
> > "unknown", its length is also unknown and should be reported as null,
> > not 0. (I would expect this also to be true in set theory when we
> > don't know what's in the set.) So now we are asking how or whether we
> > should distinguish between "unknown" and "definitely no value" in the
> > case of strings but not apparently dates, and all the standard can
> > tell us is that string lengths start at 0. Yes that implies that we
> > can use zero-length strings for "definitely no value" values, and that
> > implies that nulls should be implemented differently and that we
> > should have two kinds of emptiness as SQL Server does and Oracle does
> > not, but I wish it would come right out and say so.
>
> I think you are missing the point. A zero-length string does not mean
> "definitely no value" and no such thing is implied. A zero-length
> string IS a value. Like all values it has the property of being equal
> to itself - a property that a null does not have because a null is not
> a value.
>
> As is to be expected, the standard is completely silent on any user-
> ascribed meaning for values. Nor does the standard generally make the
> mistake of saying that null is to be used to represent
> "unknown" (except in the unfortunate case of the Boolean type - but we
> don't need to deal with that in the present discussion).
>
> > Oracle could of course implement this by (finally) changing the
> > reserved VARCHAR type, or perhaps inventing a VARCHAR3, but I'm hoping
> > that day will never come as things will get massively more complicated.
>
> For those who use only the Oracle DBMS, yes. For those who require
> interoperability and portability things would be simplified.

Perhaps I should rephrase "definitely no value". I was trying to describe a value that is known to be absent, like my wife's middle name. It seems some non-Oracle applications make use of empty string semantics to distinguish between that and, say, mine if it hasn't been entered into the system. Although I take your point that the standard doesn't make recommendations on what meaning we should read into such values, nonetheless where it is implemented it would seem to leave us with two kinds of emptiness (assuming character values in general are allowed to be null) and an opportunity to make a business distinction between them.

I'm still not convinced that my wife's middle name has a length of zero, or that it can't be said to come under the category of "not applicable", so I'm still not sure what useful purpose an empty but non-null string serves. Perhaps if I worked with SQL Server I would have a better idea of the typical uses for it. Received on Mon Aug 20 2007 - 10:37:19 CDT