Re: Oracle NULL vs '' revisited

From: Martin T. <0xCDCDCDCD_at_gmx.at>
Date: Sat, 18 Aug 2007 14:53:54 +0200
Message-ID: <46c6ec2f$0$1339$834e42db@reader.greatnowhere.com>

Serge Rielau wrote:
> Martin T. wrote:

>> If you also use other DBs besides oracle just make the column NOT NULL 
>> and treat '' as ''.
>>
>> Simple(?) as that.

> WHERE c1 = '' will behave differently.
> Unless NULL = NULL, which I recall from a past discussion is not the case.
>
> Others introduced the notion of a "known unknown". I think '' is a known
> absense of a value (I cringe since '' to me is a value like 0). NULL is
> unknown. Undecidable. It is an integral part of the predicate logic.
>
> Similar problems appear with LENGTH(SUBSTR('Hello', 1, 0)). Shouldn't it
> be 0? Just like in CONCAT NULL is "treated" as an empty string. IMHO,
> following that logic, LENGTH(NULL) = 0, alas it is not.
> For "most" functions NULL in means NULL out...
>
> Cheers
> Serge

You are of course right.
Oracle's treatment of '' necessitates a lot of workarounds and explicit checking for NULL when generically working with strings.

cheers,
Martin Received on Sat Aug 18 2007 - 07:53:54 CDT