Re: VARCHAR2: NULL value vs. empty string

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Mon, 15 Dec 2003 17:20:02 +0000
Message-ID: <38qrtvsprdj7uakp6c3ja5kbl02uubrasv@4ax.com>

On Mon, 15 Dec 2003 17:46:08 +0100, "NoName" <nobody_at_nowhere.com> wrote:

>I can assure you that empty string <> NULL value.
>According to Oracle 8.0 SQL Reference Manual, Chapter 3, paragraph about
>"Concatenation Operator":
>--- start ---
>Although Oracle treats zero-length character strings as nulls, concatenating
>a zero-length character string with another operand
>always results in the other operand, so null can result only from the
>concatenation of two null strings. However, this may not
>continue to be true in future versions of Oracle. To concatenate an
>expression that might be null, use the NVL function to
>explicitly convert the expression to a zero-length string.
>--- end ---

 If you read that more closely, you'll see that it says that no, Oracle does not distinguish '' from NULL, and is just advising you to code defensively for the time when Oracle changes the behaviour, as per the several notes in the manual.

SQL> select case

   2 when nvl(null, '') is not null then 'Empty string not null'    3 when nvl(null, '') is null then 'Empty string null' end    4 from dual;

CASEWHENNVL(NULL,'')ISNOTNULLT

---

Empty string null

 i.e. using NVL(null,'') does not make it not-null; the change has not yet been made. In particular they are warning that the following:

SQL> select 'x'||'' x, 'x'||NULL x from dual;

X X
- -
x x

 Will return the following if the change is made:

X X
- -
x

 And that if you are considering NULL as an empty string, instead of writing:

'x'||nullablefield

 ... you should be writing:

'x'||nvl(nullablefield, '')

 Which will act the same before and after the change. Received on Mon Dec 15 2003 - 11:20:02 CST