Re: Why are empty VARCHAR2S always NULL?

"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in news:ak25c0$lba$1_at_babylon.agtel.net:

> Well, 0 is not nothing. That's why NULL made it into databases. I
> agree with Richard here - if we renamed it to NOTHING or UNKNOWN, or
> better yet, NO_VALUE, there could be far less confusion as to what it
> means. And it perfectly makes sense to treat empty string as string
> with no value (hence, NULL). Same as with a number with no digits
> (empty number, not 0). You know it's a number, but don't have its
> value. You know it's a string, but don't have its value. Problem (and
> source of confusion) here is that while number with no digits is
> certainly a NULL, string with no characters is treated in most
> languages as a special case of string - an empty string - and not as
> string with no value. Most people easily accept ternary logic for
> numbers and dates and just about any other data type, but they always
> try to apply binary logic to strings even though they are working in a
> ternary logic environment and advocate that since it is so in other
> languages/environments, it should also be so in this environment just
> because they get used to the way it is elsewhere. It's like dropping
> on Mars without a life support system and demanding that air be as
> dense as on Earth because you used to be able to breathe without any
> lss...
>

Well, I'm aware of ternary logic taking place, and I make use of it, I just don't like it being used for empty strings as well. Perhaps my example with the shipped-to customer id wasn't too adequate; I used it to illustrate the need for empty strings that are not NULL, I didn't want to discuss this particular table design (be it good or poor). We have other columns that can take empty values, like, for instance, the name of a moulding tool which the user can define freely (there is no table containing the legal tool names since every possible input is legal). The user can, of course, leave the name blank if he wishes to. Sometimes users want to search for all tools except for those named 'something', so they do

        WHERE toolname!='something'

and wonder why they don't get the empty names.

I could of course use '(NONE)' or ' ' instead of an empty tool name, or make sure that

        WHERE toolname!='something' OR toolname IS NULL

is used. Just a lot of special cases where having a "real" (non-NULL) empty string would make life easier. That's why I like the comparison with the digit 0: The Romans didn't have it, they didn't see the need for it (considering it absurd to define a symbol for nothingness), and you know what their number system looked like.

Also, they didn't have a way to distinguish between a swimmer who finished
the race in zero seconds and a swimmer who didn't finish the race at all (quoting from a different posting), just like Oracle doesn't have a way to distinguish between an empty string and a missing one.

Regards
W. Roesler Received on Thu Aug 22 2002 - 06:06:22 CDT