RE: empty string = null?

From: Reardon, Bruce (CALBBAY) <Bruce.Reardon_at_comalco.riotinto.com.au>
Date: Thu, 11 Apr 2002 21:03:18 -0800
Message-ID: <F001.00442D6B.20020411210318@fatcity.com>

Jonathan,

I can't offer a solution but did come up with a simple example showing this behaviour for those (like me) who hadn't needed to investigate this before but were surprised by it.

(done under 81714 on NT4)

SQL> create table str_test ( a integer , str varchar ( 20) ); Table created.
SQL> insert into str_test values (1,null); 1 row created.
SQL> insert into str_test values (2,''); 1 row created.
SQL> select * from str_test where str is null;

         A STR

---------- --------------------
         1
         2

SQL>
SQL> select * from str_test where str = ''; no rows selected
SQL> And for the curious amongst us - here is what you get from Rdb: SQL> create table str_test ( a integer , str varchar ( 20) ); SQL> insert into str_test values (1,null); 1 row inserted
SQL>
SQL> insert into str_test values (2,''); 1 row inserted
SQL> select * from str_test where str is null;

           A   STR
           1   NULL

1 row selected
SQL> select * from str_test where str = '';

           A   STR
           2

1 row selected

I too would be interested to see how in Oracle you don't treat empty strings as nulls.

Hope this is of interest,
Bruce Reardon

-----Original Message-----
Sent: Friday, 12 April 2002 13:53

On Thu, 11 Apr 2002 17:33:18 -0800, you wrote:

>I feel I had the same dream. Anyway this from Oracle 9i doc:
>Nulls

snip
>(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.)

You're kidding! The above is really in the Oracle docs? Oracle treats zero-length strings as nulls and yet recommends against us doing likewise? How, pray tell, are we to avoid treating empty strings as nulls, since that's the way the Oracle software works?

Jonathan Gennick --- Brighten the corner where you are

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Reardon, Bruce (CALBBAY)
  INET: Bruce.Reardon_at_comalco.riotinto.com.au

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Apr 12 2002 - 00:03:18 CDT