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

Question

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?

i also follow the same "if l_version is null or l_version = '' then" — Sachu, May 27 '15 at 17:23
@Alex Yes, of course you are right. I was playing the fool with that comment :/ I will remove it in no time... — Sylvain Leroux, May 27 '15 at 18:47

score 10 · Accepted Answer · answered May 27 '15 at 18:13

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.

score 6 · Answer 2 · answered May 27 '15 at 18:11

6

If the behaviour does change then are you not going to want to be able to distinguish between empty string and null when such a thing is possible? In other words, future proof code that treats empty strings and nulls as the same will probably need revisiting in the future anyway.

Also, there are currently something like 100 billion trillion null strings stored in Oracle databases worldwide that will continue to be null.

So my advice is to forget about it, and just use IS NULL.

answered May 27 '15 at 18:11

David Aldridge

51,479
8
68
96

In my particular example I don't want to distinguish. If `getVersion` returns `null` or an empty `varchar2` I will fill in a hardcoded default value(see code in the question). – Roland May 28 '15 at 12:59
Sure -- I'm just aiming at the the wider issue. – David Aldridge May 28 '15 at 13:13

score 1 · Answer 3 · answered May 27 '15 at 20:27

Sounds like a lawyer got into Oracle's documentation. I can't imagine in a million years that Oracle would suddenly change how they handle empty strings with varchar2, a TON of code would break (and much of it silently). This statement in the docs seems to be a "cover your a$$" just in case. So, just continue to use "is null" and don't worry. If anything, they'll change varchar behavior, not varchar2.

My 2 cents anyway.

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

3 Answers3