Re: Nulls get converted to 0 problem

On Thu, 5 Jun 2003, Jon Earle wrote:

> On Wed, 4 Jun 2003 terry@ashtonwoodshomes.com wrote:
>
> > Oracle *incorrectly* interprets blank (empty) strings as NULL.  They are NOT
> > the same.  A string of zero characters is a string nonetheless.  A NULL is
> > "the absence of value", which equals nothing (theoretically not even another
> > NULL).
>
> If you're testing a value, you're testing to see if there's something in
> there or not - what difference does it make if the variable contains 0, ""
> or NULL?

Every interface I know of in every language (except cold fusion) has a
test for null.  There IS a difference, and it's not a difference of just
semantics, it has real world meaning.

Enter a record for me.  Enter my cell phone number.  It's a text type.  If
you enter a NULL you are saying I may or may not have a cell phone, you
don't know.  If you enter '' you are saying that I do NOT have a cell
phone.

Hey, who has a cell phone we don't have numbers for?

select * from table where cell_phone IS NULL;

I don't have to make up a boolean to say what I mean when I put in a '' or
a NULL.

For numbers, a NULL should never be coerced to 0, which is what was
happening to Ari due to the older jdbc driver.  Since blank numeric and
date types aren't allowed there's no confusion issue.  But for text there
certainly is a difference in meaning.