8.3 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.

8.3.1 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 in from the database:

DECLARE

user_entered_name VARCHAR2(30);

name_from_database VARCHAR2(30);

...

BEGIN

...

IF user_entered_name <> name_from_database THEN

...

If the user entered an empty string instead of a name, the IF condition shown in this example would never be TRUE. That's because a NULL is never not-equal, nor equal, to any other value. One alternative approach to this IF statement is the following:

IF (user_entered_name <> name_from_database)

OR (user_entered_name IS NULL) THEN

This is just one way of dealing with the "empty string is NULL" issue; it's impossible to provide a solution that works in all cases. You must think through what you are trying to accomplish, recognize that any empty strings will be treated as NULLs, and code appropriately.

8.3.2 Mixing char and varchar2 Values

If you make use of both fixed-length (CHAR) and variable-length (VARCHAR2) strings in your PL/SQL code, you should be aware of how Oracle handles the interactions between these two datatypes, as described in the following sections.

8.3.2.1 Database-to-variable conversion

When you SELECT or FETCH data from a CHARdatabase column into a VARCHAR2 variable, the trailing spaces are retained. If you SELECT or FETCH from a VARCHAR2 database column into a CHAR variable, PL/SQL automatically pads the value with spaces out to the maximum length. In other words, the type of the variable, not the column, determines the variable's resulting value.

8.3.2.2 Variable-to-database conversion

When you INSERT or UPDATE a CHAR variable into a VARCHAR2 database column, the SQL kernel does not trim the trailing blanks before performing the change. When the following PL/SQL is executed, the company_name in the new database record is set to "ACME SHOWERS········" (where · indicates a space). It is, in other words, padded out to 20 characters, even though the default value was a string of only 12 characters.

DECLARE

comp_id# NUMBER;

comp_name CHAR(20) := 'ACME SHOWERS';

BEGIN

SELECT company_id_seq.NEXTVAL

INTO comp_id#

FROM dual;

INSERT INTO company (company_id, company_name)

VALUES (comp_id#, comp_name);

END;

On the other hand, when you INSERT or UPDATE a VARCHAR2 variable into a CHAR database column, the SQL kernel automatically pads the variable-length string with spaces out to the maximum (fixed) length specified when the table was created, and places that expanded value into the database.