String comparisons

Suppose your code contains a string comparison such as the following:

IF company_name = parent_company_name ...

PL/SQL must compare company_name to parent_company_name. It performs the comparison in one of two ways, depending on the types of the two variables:

· If a comparison is made between two CHAR variables, then PL/SQL uses ablank-padding comparison.

· If at least one of the strings involved in the comparison is variable-length, then PL/SQL performs anon-blank-padding comparison.

The following code snippet illustrates the difference between these two comparison methods:

DECLARE company_name CHAR(30) := 'Feuerstein and Friends'; char_parent_company_name CHAR(35) := 'Feuerstein and Friends'; varchar2_parent_company_name VARCHAR2(35) := 'Feuerstein and Friends';BEGIN --Compare two CHARs, so blank-padding is used IF company_name = char_parent_company_name THEN DBMS_OUTPUT.PUT_LINE ('first comparison is TRUE'); ELSE DBMS_OUTPUT.PUT_LINE ('first comparison is FALSE'); END IF; --Compare a CHAR and a VARCHAR2, so nonblank-padding is used IF company_name = varchar2_parent_company_name THEN DBMS_OUTPUT.PUT_LINE ('second comparison is TRUE'); ELSE DBMS_OUTPUT.PUT_LINE ('second comparison is FALSE'); END IF;END;

The output is:

first comparison is TRUEsecond comparison is FALSE

The first comparison is between two CHAR values, so blank-padding is used: PL/SQL blank-pads the shorter of the two values out to the length of the longer value. It then performs the comparison. In this example, PL/SQL adds five spaces to the end of the value in company_name and then performs the comparison between company_name and char_parent_company_name. The result is that both strings are considered equal. Note that PL/SQL does not actually change the company_name variable's value. It copies the value to another memory structure and then modifies this temporary data for the comparison.

The second comparison involves a VARCHAR2 value, so PL/SQL performs a non-blank-padding comparison. It makes no changes to any of the values, uses the existing lengths, and performs the comparison. In this case, the first 22 characters of both strings are the same, "Feuerstein and Friends", but the fixed-length company_name is padded with eight space characters, whereas the variable-length varchar2_company_name is not. Because one string has trailing blanks and the other does not, the two strings are not considered equal.

The fact that one VARCHAR2 value causesnon-blank-padding comparisons is also true of expressions involving more than two variables, as well as of expressions involving the IN operator. For example:

IF menu_selection NOT IN (save_and_close, cancel_and_exit, 'OPEN_SCREEN') THEN ...

If any of the four strings in this example (menu_selection, the two named constants, and the single literal) is declared VARCHAR2, then exact comparisons without modification are performed to determine if the user has made a valid selection. Note that a literal likeOPEN_SCREEN is always considered a fixed-length CHAR datatype.