Re: NULL versus empty string

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 - 19:51:08 CDT