PL/SQL User's Guide and Reference

Comparing Character Values

You can use the relational operators to compare character values for equality or inequality. Comparisons are based on the collating sequence used for the database character set. One character value is greater than another if it follows it in the collating sequence. For example, given the declarations

name1 VARCHAR2(10) := 'COLES';

name2 VARCHAR2(10) := 'COLEMAN';

the following IF condition is true:

IF name1 > name2 THEN ...

ANSI/ISO SQL requires that two character values being compared have equal lengths. So, if both values in a comparison have datatype CHAR, blank-padding semantics are used. That is, before comparing character values of unequal length, PL/SQL blank-pads the shorter value to the length of the longer value. For example, given the declarations

name1 CHAR(5) := 'BELLO';

name2 CHAR(10) := 'BELLO   ';  -- note trailing blanks

the following IF condition is true:

IF name1 = name2 THEN ...

If either or both values in a comparison have datatype VARCHAR2, non-blank-padding semantics are used. That is, when comparing character values of unequal length, PL/SQL makes no adjustments and uses the exact lengths. For example, given the declarations

name1 VARCHAR2(10) := 'DOW';

name2 VARCHAR2(10) := 'DOW   ';  -- note trailing blanks

the following IF condition is false:

IF name1 = name2 THEN ...

If one value in a comparison has datatype VARCHAR2 and the other value has datatype CHAR, non-blank-padding semantics are used. But, remember, when you assign a character value to a CHAR variable, if the value is shorter than the declared length of the variable, PL/SQL blank-pads the value to the declared length. So, given the declarations

name1 VARCHAR2(10) := 'STAUB';

name2 CHAR(10)     := 'STAUB';  -- PL/SQL blank-pads value

the following IF condition is false because the value of name2 includes five trailing blanks:

IF name1 = name2 THEN ...

All string literals have datatype CHAR. So, if both values in a comparison are literals, blank-padding semantics are used. If one value is a literal, blank-padding semantics are used only if the other value has datatype CHAR.