Re: Oracle NULL vs '' revisited

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...

Yes, everybody uses the middle name example (although I'm missing the relevance of the country of birth). True, it isn't an unknown value, but I'm still not convinced the length is 0, that the number of names is 3, or indeed that the US-born lovechild's middle name is legally ''. Perhaps if I worked more with SQL Server I would see things differently. Are there any other examples?

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. Received on Thu Aug 23 2007 - 07:20:52 CDT