NULL value EQUALS to EMPTY string?!

Hi!

It's notorious that table cells may have the special value null (or unknown).
This value is different from the number 0, and it is also different from the empty string ''.

But...

SQL> create table test2(
  2 n number,
  3 c char(5),
  4 v varchar2(5),
  5 d date);

Table created.

SQL> insert into test2 values(1, null, null, null);

1 row created.

SQL> insert into test2 values(2, '', '', '');

1 row created.

SQL> commit;

SQL> select * from test2;

         N C V D
---------- ----- ----- ---------

         1
         2

SQL> select * from test2 where c is null;

         N C V D
---------- ----- ----- ---------

         1
         2

SQL> select * from test2 where v is null;

         N C V D
---------- ----- ----- ---------

         1
         2

SQL> select * from test2 where d is null;

         N C V D
---------- ----- ----- ---------

         1
         2

SQL> select * from test2 where c='';

no rows selected

SQL> select * from test2 where v='';

no rows selected

SQL> select * from test2 where d='';

no rows selected

SQL> insert into test2 values(null, null, null, null);

1 row created.

SQL> commit;

SQL> select * from test2 where n is null;

         N C V D
---------- ----- ----- ---------

SQL> select * from test2 where n='';

no rows selected

SQL> So, NULL is equal to '' for strings
and is not equal to '' for numbers!

Have you any comments?
Is it possible to insert NULL value into char, varchar2?

Klim.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Feb 07 2000 - 07:24:00 CST