Re: NULL value EQUALS to EMPTY string?!

I've got my server powered down now, otherwise I'd demonstrate this:

Re-create the table with NOT NULL constraints, then insert '' into them. That should answer the question.

Klim Samgin <klimsamgin_at_yahoo.com> wrote in message news:87mh2h$gl1$1_at_nnrp1.deja.com...
> 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 Wed Feb 09 2000 - 19:54:34 CST