Re: Oracle NULL vs '' revisited

From: Martin T. <0xCDCDCDCD_at_gmx.at>
Date: Fri, 24 Aug 2007 17:11:42 +0200
Message-ID: <46cef5fd$0$1343$834e42db@reader.greatnowhere.com>

William Robertson wrote:

> On Aug 24, 9:34 am, "Martin T." <0xCDCDC..._at_gmx.at> wrote:
>> William Robertson wrote:
>>> On Aug 23, 3:13 pm, "Martin T." <0xCDCDC..._at_gmx.at> 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
>>
>> cheers,
>> Martin
> 
> I'm still struggling to find an example of a non-null empty string,
> especially if we're agreed that no-middle-name guy has two names and
> not three. If we had one of those then it might be easier to agree
> that its length should be 0.
> 

The (for me) intuitive way:

---

SELECT * from MY_TABLE
WHERE LENGTH(VARCHAR_COLUMN) = LENGTH(:p_search_equal_length_string);

The Oracle way:

---

SELECT * from MY_TABLE
WHERE LENGTH(VARCHAR_COLUMN) = LENGTH(:p_search_equal_length_string)

    OR (VARCHAR_COLUMN IS NULL AND :p_search_equal_length_string IS NULL);

... I'm not even sure that oracle statement does what it's supposed to do.

Too far fetched?

br,
Martin Received on Fri Aug 24 2007 - 10:11:42 CDT