Re: VARCHAR2: NULL value vs. empty string

From: Frank <fbortel_at_nescape.net>
Date: Sat, 13 Dec 2003 16:34:26 +0100
Message-ID: <brfb1b$4bh$1@news4.tilbu1.nb.home.nl>

Daniel Morgan wrote:

> Frank wrote:
>

>> Keith Sauvant wrote:
>>
>>> Hi Group,
>>>
>>> is there _any_ way (Server Parameter?) to make Oracle store empty 
>>> strings in Varchar2s without converting them to NULL?
>>>
>>> Thanks in advance
>>> Keith Sauvant
>>>
>>
>> No. Oracle != MS SQL Server (And not ANSI compliant in this matter)

>
>
> This has nothing to do with ANSI compliance. Where did you ever get the
> idea that it did?
>
> I'd suggest you read page 38 of Tom Kyte's "Expert one-on-one Oracle."
>

Oracle is not MS SQL server (and [Oracle] is not ANSI compliant in this matter).
The ANSI standard defines an empty string as defined (which is not the same as undefined, or NULL). Oracle still regards an empty string as undefined.

Indeed, the OP problem has nothing to do with ANSI compliance; I just noted that Oracle wasn't ANSI compliant in this matter, which kind of surprised me too, when I found out.

Page 38/39 are about NULLs and NULL comparison - not about empty strings, whatever gave you that idea?

And VC has given enough links to prove otherwise, if not, check your own; the first link refers to a table, composed based on an article, which states:
<quote>
For E131: Oracle won't distinguish NULLs from blank strings. </quote>
It's in the Core Feature section of the article: where it says <quote>
This section shows the exceptional cases where at least one DBMS misses a core SQL:1999 feature.
</quote>

Based on this, I'm even inclined to say, that the first link quotes it's source wrongly.

Your second link does not involve strings, either. As for your conclusion, that '' does indeed equal '', try this - this is 9.2.0.4 on Linux:

SQL> select nvl('','Is Null') from dual;

NVL('',

---

Is Null

SQL> select nvl(NULL,'Is Null') from dual;

NVL(NUL

---

Is Null

SQL> select 1 from dual where '' IS NULL;

          1

---

          1

SQL> select 1 from dual where '' = NULL;

no rows selected

Oracle regards '' as NULL. Even your conclusion is easily proven wrong:
SQL> select 1 from dual where '' = '' ;

no rows selected

'' is NOT equal ''

QED, rmc, eod

-- 
Regards, Frank van Bortel

Received on Sat Dec 13 2003 - 09:34:26 CST