Re: Oracle NULL vs '' revisited

Matthew Harrison wrote:
> Ok, I'm developing another oracle application where the distinction
> between NULL and '' will be important.
>
> Given Oracle doesn't adhere to SQL standards for the distinction, is
> there any best practices for comparing, and storing blank strings in a
> table where the field is conceptually NOT NULL.
>
> Thank you.

SQL> create table t (

   2 col1 varchar2(10),
   3 col2 varchar2(10));

Table created.

SQL> INSERT INTO t VALUES (NULL, '');

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT sys_op_map_nonnull(col1), sys_op_map_nonnull(col2)

   2 FROM t;

SYS_OP_MAP_NONNULL(COL SYS_OP_MAP_NONNULL(COL

---------------------- ----------------------
FF                     FF

SQL> Not unless you create a kludge. For example insert 'ZZYZX' where ever you intend an empty string.

The better choice would be, no matter the database product, to never have NULLs or empty strings. This can be done with proper design.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org