Re: Oracle NULL vs '' revisited

From: Martin T. <0xCDCDCDCD_at_gmx.at>
Date: Thu, 23 Aug 2007 16:13:11 +0200
Message-ID: <46cd966d$0$1347$834e42db@reader.greatnowhere.com>

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 Received on Thu Aug 23 2007 - 09:13:11 CDT