Character strings are compared according to the collating sequence specified when the database was created, except those with a FOR BIT DATA attribute which are always compared according to their bit values.
When comparing character strings of unequal lengths, the comparison is made using a logical copy of the shorter string which is padded on the right with single-byte blanks sufficient to extend its length to that of the longer string. This logical extension is done for all character strings including those tagged as FOR BIT DATA.
Character strings (except character strings tagged as FOR BIT DATA) are compared according to the collating sequence specified when the database was created (see the Administration Guide for more information on collating sequences specified at database creation time). For example, the default collating sequence supplied by the database manager may give lowercase and uppercase versions of the same character the same weight. The database manager performs a two-pass comparison to ensure that only identical strings are considered equal to each other. In the first pass, strings are compared according to the database collating sequence. If the weights of the characters in the strings are equal, a second "tie-breaker" pass is performed to compare the strings on the basis of their actual code point values.
Two strings are equal if they are both empty or if all corresponding bytes are equal. If either operand is null, the result is unknown.
Long strings and LOB strings are not supported in any comparison operations that use the basic comparison operators (=, <>, <, >, <=, and >=). They are supported in comparisons using the LIKE predicate and the POSSTR function. See LIKE Predicate and see POSSTR for details.
Portions of long strings and LOB strings of up to 4000 bytes can be compared using the SUBSTR and VARCHAR scalar functions. For example, given the columns:
MY_SHORT_CLOB CLOB(300) MY_LONG_VAR LONG VARCHAR
then the following is valid:
WHERE VARCHAR(MY_SHORT_CLOB) > VARCHAR(SUBSTR(MY_LONG_VAR,1,300))
Examples:
For these examples, 'A', 'B', 'a', and 'b', have the code point values X'41', X'42', X'61', and X'62' respectively.
Consider a collating sequence where the characters 'A', 'B', 'a', 'b' have weights 75, 101, 74, and 100. Then:
'a' < 'A' < 'b' < 'B'
and
'aa' < 'aA' < 'ab' < 'aB' < 'Aa' < 'AA' < 'Ab' < 'AB'.
However, if the values being compared have the FOR BIT DATA attribute, the collating sequence is ignored, and:
'A' < 'B' < 'a' < 'b'
and
'AA' < 'AB' < 'Aa' < 'Ab' < 'aA' < 'aB' < 'aa' < 'ab'.
Now consider a collating sequence where the characters 'A', 'B', 'a', 'b' have (non-unique) weights 74, 75, 74, and 75. Then:
'A' < 'a' < 'B' < 'b'
and
'AA' < 'Aa' < 'aA' < 'aa' < 'AB' < 'Ab' < 'aB' < 'ab'.