On Aug 24, 4:11 pm, "Martin T." <0xCDCDC..._at_gmx.at> wrote:
> 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
Well, that is because null means unknown so we can't say what the
length is - unless it can also mean known-to-be-empty, which is what
this whole thread is about. I was hoping someone could come up with
some more examples as, if you recall, neither of us were convinced by
the no-middle-name guy one.
Received on Sat Aug 25 2007 - 10:17:17 CDT
