Search code examples
oracleplsqlvarcharvarchar2

What is the correct way of checking if varchar2 is empty?

According to official 11g docs

Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.

Consider a function getVersion that returns a varchar2 that might possibly be '':

l_version := x.getVersion;
if l_version is null then
  return 'V.1.0';
end if;

This will work correctly on current Oracle 11g, but might break as soon as future Oracle versions treat '' differently than null.

The only way I see to do the above future proof is:

if l_version is null or l_version = '' then

Is there a less cumbersome way?

Solution

  • Assuming that you are using varchar2 throughout your code, l_version is null will be future proof.

    Oracle created the varchar2 data type when the ANSI standards declared that varchar should treat NULL and the empty string as separate entities. The intention was that the behavior of the varchar2 data type would remain consistent going forward while varchar in the future could use the new standard NULL comparison semantics. Of course, today varchar and varchar2 are synonyms for each other and they have been for at least a couple of decades so the odds that Oracle actually changes the behavior of the varchar data type in the future is pretty low.

    When you look at the documentation for the VARCHAR2 and VARCHAR data types, it talks about the comparison semantics for VARCHAR potentially changing in the future. Unfortunately, it's not explicit that the comparison semantics they're talking about are the equivalence (or lack thereof) between NULL and the empty string. But since VARCHAR is an ANSI standard data type and the only difference in VARCHAR comparison semantics between Oracle and the ANSI standard is whether the empty string is NULL, that's the generally accepted interpretation.

    Do not use the VARCHAR datatype. Use the VARCHAR2 datatype instead. Although the VARCHAR datatype is currently synonymous with VARCHAR2, the VARCHAR datatype is scheduled to be redefined as a separate datatype used for variable-length character strings compared with different comparison semantics.