Re: NULL vs. empty

From: Frank van Bortel <fvanbortel_at_netscape.net>
Date: Mon, 24 May 2004 21:19:45 +0200
Message-ID: <c8thkv$rc8$1@news2.tilbu1.nb.home.nl>

Steffen Conrad wrote:

> Hi all,
>
> I have the following problem:
>
> We are loading ASN.1 coded data into 8i, runnning updates etc. on the
> loaded data and unloading it. When doing this we have to assure handling
> of empty fields in the ASN.1-coded data.
>
> Explanation:
>
> (1) no data in the ASN.1 coded data -> save it as NULL on DB
> (2) data in ASN.1 -> save value on DB
> (3) only tags without data in ASN.1 coded data -> save as 'empty' on DB,
> but not NULL
>
> We are using SQLLDR to load data onto DB, for strings we are able to
> divide between NULL and 'empty' fields by passing ,, (for NULL ASN.1
> fields) and ,"\t", (for empty ASN.1 fields). This is a workaround
> because when unloading the data will be parsed as ASCII and blanks/tabs
> will be not read when working on unloaded data.
>
> But how to do this for NUMBERS/INTEGER? Is there a way for passing a
> special value to the SQLLDR like 'NaN' or 'inf' etc. for determining
> empty (but not NULL) fields.
>
> SQLLDR stops working when passing "" or "\t" for empty fields.
>
> What is the best way to handle this problem on DB and for SQLLDR?
> Please do not mention about an additional column for each value... ;-)
>
> Bye,
> Steffen

Oracle treats empty strings as NULLs (OK Daniel...), at least for character columns (CHAR, VARCHAR, VARCHAR2, NVARCHAR, NVARCHAR2):

SQL> create table bla (col1 varchar2(10)); Table created.

SQL> insert into bla values ('');
1 row created.

SQL> commit;
Commit complete.

SQL> select count(*) from bla where col1 is null;

   COUNT(*)

---

          1
SQL> select count(*) from bla where col1 is not null;

   COUNT(*)

---

          0
SQL> select nvl('','Is NULL') from dual; NVL('','ISNULL')

---

Is NULL

SQL> select nvl(' ','Is NULL') from dual; NVL

---

SQL>
-- 

Regards,
Frank van Bortel

Received on Mon May 24 2004 - 14:19:45 CDT