Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle NULL vs '' revisited

Re: Oracle NULL vs '' revisited

From: Martin T. <>
Date: Fri, 24 Aug 2007 10:34:12 +0200
Message-ID: <46ce983d$0$1341$>

William Robertson wrote:

> On Aug 23, 3:13 pm, "Martin T." <> wrote:

>> William Robertson wrote:
>>> On Aug 22, 7:22 pm, "Paul Linehan" <plinehan__A_at_T__yahoo__D.OT__COM>
>>> wrote:
>>>> Mark D Powell wrote:
>>>>> I have always had difficulty with the concept that an empty string
>>>>> should not be considered a NULL value to begin with. What does an
>>>>> empty string hold?
>>>> Take an ex-girlfriend of mine, she had a child in the States
>>>> and *_specifically_* didn't give her (the child) a middle name.
>>>> So, Middle_Initial is blank '' - and not NULL, since it is a
>>>> known quantity - as Donald Rumsfeld might say, a "known unknown".
>>>> NULLs are unknown unknowns. Despite Mr. Rumsfeld's verbal
>>>> gymnastics, there is no such thing as an unknown known.
>>>> You concatenate blank with a string, and you simply get the string
>>>> back - do the same with NULL and you get NULL.
>>>> I'd say NULLs are readily distinguishable from blank strings.
>>>> Paul...
>>> (...)
>>> Oracle treats nulls as empty strings when concatenating, often leading
>>> to cries of inconsistency in this sort of debate, but it is the
>>> overwhelmingly more useful behaviour.
>> Yeah. And Length('') == NULL which is bloody awful.
>> Imho, Oracle just messed up on varchar2 and NULL. (from a practical, not
>> a philosophical point of view)
>> br,
>> Martin

> Well as theses debates always show it can be debated endlessly, but

Well yes. It's what we are doing atm, isn't it? :-)

> surely a length of 0 is incorrect for an unknown value; and even in

Which is exactly the problem with Oracle. LENGTH(NULL) should be NULL but LENGTH('') be better off being 0 (for all string processing puroses I can think of atm) which is o.c. not possible in Oracle.

> the case of specifically-no-middle-name guy, if we are not including
> it in a count (how many names does he have?) surely it would be
> inconsistent to give it a length (what's the average length of his
> names?)

I must say I really don't get the middle-name example, sorry. It's a strange analogy gone rampant, if you ask me. :-P

Martin Received on Fri Aug 24 2007 - 03:34:12 CDT

Original text of this message