Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle NULL vs '' revisited

Re: Oracle NULL vs '' revisited

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 17 Aug 2007 09:56:24 -0700
Message-ID: <1187369783.555739@bubbleator.drizzle.com>


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
Received on Fri Aug 17 2007 - 11:56:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US