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 ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Read now
Unlock full access