NULL string handling

From: Brian Gladish <gladish_at_elvis.suite.com>
Date: 1995/04/22
Message-ID: <3n9i6b$k2l_at_news.onramp.net>#1/1

I'm quite surprised at Oracle's handling of NULL strings in the following example:

---

SQL> create table tempg (a char(10) null, b char(10) null);

Table created.

SQL> insert into tempg values (null, 'Not NULL');

1 row created.

SQL> insert into tempg values ('', 'Not NULL');

1 row created.

SQL> insert into tempg values('Not NULL', null);

1 row created.

SQL> select * from tempg;

A B
---------- ----------

           Not NULL
           Not NULL

Not NULL

SQL> select a || b from tempg;

A||B

---

Not NULL
Not NULL
Not NULL

SQL> select * from tempg where a is null;

A B
---------- ----------

           Not NULL
           Not NULL

SQL>

---

I believe that NULL || <anything> should be NULL, and that '' (the empty string) should not return TRUE for IS NULL. Is there an option EMPTY_STRING_IS_NOT_NULL or an IS REALLY NULL operator? Am I missing something?

BTW, Sybase has a similar problem with a little twist - the empty string ends up being ' ' (one space) and, therefore, NOT NULL (but not quite right, either).

Brian
gladish_at_suite.com Received on Sat Apr 22 1995 - 00:00:00 CEST