Re: VARCHAR2: NULL value vs. empty string

> If NULL is not a zero length string, then why does Oracle do this? :-
> SQL> select 'abc'||null from dual;
>
> 'AB
> ---
> abc
>
> It should return NULL, should it not?

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 ---

Regards Received on Mon Dec 15 2003 - 10:46:08 CST