Oracle PL/SQL Programming, Third Edition

String Issues

Most of the time, working with strings is very straightforward. However, there are some subtle issues you should be aware of, as described in the next few sections.

Empty Strings Are NULL Strings

One issue that often causes great consternation, especially to people who come to Oracle after working with other databases, is that Oracle treats empty strings as NULLs. This is contrary to the ANSI SQL standard, which recognizes the difference between an empty string and a string variable that is NULL.

The following code demonstrates Oracle’s behavior:

/* File on web: empty_is_null.tst */
DECLARE
   empty_varchar2 VARCHAR2(10) := '';
   empty_char CHAR(10) := '';
BEGIN
   IF empty_varchar2 IS NULL THEN
      DBMS_OUTPUT.PUT_LINE('empty_varchar2 is NULL');
   END IF;
   
   IF '' IS NULL THEN
      DBMS_OUTPUT.PUT_LINE(''''' is NULL');
   END IF;

   IF empty_char IS NULL THEN
      DBMS_OUTPUT.PUT_LINE('empty_char is NULL');
   END IF;
END;

The output is:

empty_varchar2 is NULL
'' is NULL

You’ll notice in this example that the CHAR variable is not considered NULL. That’s because CHAR variables, as fixed-length character strings, are never truly empty. The CHAR variable in this example is padded with blanks until it is exactly 10 characters in length. The VARCHAR2 variable, however, is NULL, as is the zero-length string literal.

You have to really watch for this behavior in IF statements that compare two VARCHAR2 values. Consider a program that queries the user for a name, and then compares that name to a value read ...