Over a million developers have joined DZone.

PL/SQL treads zerolength string as NULL values


There is no such thing as a zero-length string in Oracle PL/SQL. A zero-length string would always result in a NULL-String. The code above tests and proves this.

Cautionary statement

You should not use a comparison operator like =, <>, <, > before asserting that both strings used in the expression are not NULL. See the further comment on:


If your coding in different languages is parallel, this statement is especially important to be aware of.


Othmar Lippuner

SQL Reporting specialist 






declare str varchar2(128);
str := null;
dbms_output.put_line('sample-1>>' || length(str)|| '<<');
str := '';
dbms_output.put_line('sample-2>>' || length(str)|| '<< !!!');
if length(str) = 0 then
  dbms_output.put_line('String length is 0'); 
  dbms_output.put_line('String length is undefined'); 
end if;
     ('Where as non-empty strings perform as excepted'); 
str := 'A';dbms_output.put_line( str || '>>' || length(str));
str := 'Beware of dealing with zerolenght strings in PL/SQL ';
dbms_output.put_line( str || '>>' || length(str)|| '<<');


{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}