Zero length (empty) strings are NULL
Oracle considers a zero length
varchar2 to be the
null value.
When used to string handling in other databases such as
SQL Server, this behavior might be a bit irittating.
A simple demonstration on handling NULL values in Oracle SQL:
First, we need a table and some values:
create table tq84_null_test (
text_1 varchar2(20),
text_2 varchar2(20)
);
insert into tq84_null_test values ('hello', 'world' );
insert into tq84_null_test values (null , 'other is null' );
insert into tq84_null_test values ('' , 'other is empty');
commit;
We try to select the records where
val_1 is the empty string. Because Oracle treats empty strings as
null, the where condition in the following
SQL statement is equivalent to
where val_1 = null; and because
null value is
never equal to any other value, the following statement returns no records at all:
select * from tq84_null_test where text_1 = '';
However, if we use val_1 is null, then we get two records.
select * from tq84_null_test where text_1 is null;
--
-- TEXT_1 TEXT_2
-- -------------------- --------------------
-- other is null
-- other is empty
In Oracle, concatenating a string with NULL does not evaluate to NULL:
select
text_1 || text_2 || NULL concatenated
from
tq84_null_test;
--
-- CONCATENATED
-- ----------------------------------------
-- helloworld
-- other is null
-- other is empty