Re: Easy SQL problem

Hi Daniel, Ben and all,

Quick demo.

SQL> create table null_or_not (id number, name varchar(10));

Table created.

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

1 row created.

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

1 row created.

SQL> commit;

SQL> col dump_name format a20
SQL> select id, name, dump(name) dump_name from null_or_not;

        ID NAME DUMP_NAME

---------- ---------- --------------------
         1            NULL
         2            NULL

So '' is being treated as a NULL.

However, it is bad programming practice to use the second method. Oracle has it clearly documented that it may not continue to treat a zero length sting as NULL in the future.

Cheers

Richard
Daniel Morgan wrote:
>
> Ben wrote:
>
> > Any one know if this is a new 9i feature or not?
> >
> > I have the following table.
> >
> > SQL> desc table1
> > Name Null? Type
> > ----------------------------------------------------- --------
> > -----------
> > ID
> > VARCHAR2(5)
> > NAME
> > VARCHAR2(5)
> >
> > SQL> insert into table1 values ('242','');
> >
> > 1 row created.
> >
> > SQL> select * from table1;
> >
> > ID NAME
> > ----- ------------------------------
> > 1 A
> > 2 A
> > 3 C
> > 4 D
> > 5. E
> > aaa C
> > 242
> >
> > 7 rows selected.
> >
> > SQL> select * from table1 where name is null;
> >
> > ID NAME
> > ----- ------------------------------
> > 242
> >
> > SQL> select * from table1 where name is not null;
> >
> > ID NAME
> > ----- ------------------------------
> > 1 A
> > 2 A
> > 3 C
> > 4 D
> > 5. E
> > aaa C
> >
> > 6 rows selected.
> >
> > SQL> select * from table1 where name <> '';
> >
> > no rows selected
> >
> > SQL> select * from table1 where name = '';
> >
> > no rows selected
> >
> > My question is why do I get no rows selected for "select * from table1
> > where name <> '';" and "select * from table1 where name = '';"?
> >
> > Any idea?
> >
> > Thanks!!
> > Ben
>
> What makes you think that '' is NULL?
>
> Hopefully nothing but a brief memory lapse.
>
> Your insert statement should have been:
>
> insert into table1 values ('242', NULL);
>
> Daniel Morgan