Re: NULL versus empty string

I would guess that the logic is that if you don't want to allow null, then you want an actual value and "" while not being null, is not really a value conceptually. I mean, if "" was acceptable, why not just allow null?

David Wall wrote:

> The 8i docs don't help much.... From Oracle 8i SQL Reference Release 3
> (8.1.7), section 2
>
> <quote>
> "VARCHAR2(size) Variable-length character string having maximum length size
> bytes. Maximum size is 4000, and minimum is 1. You must specify size for
> VARCHAR2."
> </quote>
>
> So it sounds like a VARCHAR cannot have a zero length.
>
> <quote>
> "The VARCHAR2 datatype specifies a variable-length character string. When
> you create a VARCHAR2 column, you supply the maximum number of bytes of data
> that it can hold. Oracle subsequently stores each value in the column
> exactly as you specify it, provided the value does not exceed the column's
> maximum length. If you try to insert a value that exceeds the specified
> length, Oracle returns an error.
>
> You must specify a maximum length for a VARCHAR2 column. This maximum must
> be at least 1 byte, although the actual length of the string stored is
> permitted to be zero. The maximum length of VARCHAR2 data is 4000 bytes.
> Oracle compares VARCHAR2 values using nonpadded comparison semantics."
> </quote>
>
> So, the actual length of the string stored is permitted to be zero, so an
> empty string should be okay.
>
> <quote>
> "If a column in a row has no value, then the column is said to be null, or
> to contain a null. Nulls can appear in columns of any datatype that are not
> restricted by NOT NULL or PRIMARY KEY integrity constraints. Use a null when
> the actual value is not known or when a value would not be meaningful.
>
> Do not use null to represent a value of zero, because they are not
> equivalent. (Oracle currently treats a character value with a length of zero
> as null. However, this may not continue to be true in future releases, and
> Oracle recommends that you do not treat empty strings the same as nulls.)
> Any arithmetic expression containing a null always evaluates to null. For
> example, null added to 10 is null. In fact, all operators (except
> concatenation) return null when given a null operand."
> </quote>
>
> Exactly, they are NOT the same thing, but the parenthetical comments say
> that Oracle, in fact, does. So Oracle recommends that I not treat the same,
> yet there is no obvious mechanism on how to accomplish other than to
> translate every string in and out of the database so that an empty string is
> really stored as a single space, and then on retrieve converting the single
> space back to an empty string. That's rather a major pain since most data
> stored and retrieved are strings that will have this potential issue.
>
> David
>
>
>
Received on Mon Sep 23 2002 - 20:00:19 CDT