Character data is compared based on its character value, and two types of measurement are involved: binary and linguistic comparisons, as well as blank-padded or nonblank-padded comparison semantics. This section describes the comparison rules for character data based on these two types of measurement.
Binary and linguistic comparisons
Binary comparison
In the default binary comparison, OceanBase Database compares strings based on the concatenated numeric codes of characters in the database character set. If the numeric value of a character in the character set is greater than that of another character, the former is considered greater. OceanBase Database does not support the ASCII and EBCDIC character sets.
Linguistic comparison
In linguistic sorting, SQL sorting and comparisons follow the language rules specified by the NLS_SORT parameter. If the binary sequence of character encoding does not match the language sequence required by the character set, linguistic comparison is used. If the NLS_SORT parameter is not set to BINARY and the NLS_COMP parameter is set to LINGUISTIC, linguistic comparison is used.
Blank-padded and nonblank-padded comparison semantics
Blank-padded comparison semantics
With blank-padded comparison semantics, if the lengths of two values are different, OceanBase Database appends spaces to the end of the shorter value to make the lengths equal. Then, it compares the two values character by character until it finds the first differing character. The value with the larger character at this position is considered greater. If there are no differing characters, the values are considered equal. This rule implies that two values are equal only if they have the same number of trailing spaces.
Notice
OceanBase Database uses blank-padded comparison semantics only when both values being compared are of the CHAR, NCHAR, TEXT literal, or value returned by the USER function.
Nonblank-padded comparison semantics
With nonblank-padded comparison semantics, OceanBase Database compares the two values character by character until it finds the first differing character. The value with the larger character at this position is considered greater. If two values of different lengths are the same up to the length of the shorter value, the longer value is considered greater. If two values of the same length have no differing characters, they are considered equal.
Notice
OceanBase Database uses nonblank-padded comparison semantics when one or both of the values being compared are of the VARCHAR2 or NVARCHAR2 data type.
The results of comparing two character values can vary depending on the comparison semantics used. The following table shows the results of comparisons using blank-padded and nonblank-padded semantics.
| Blank-padded | Nonblank-padded |
|---|---|
| 'ac' > 'ab' | 'ac' > 'ab' |
| 'ab' > 'a ' | 'ab' > 'a ' |
| 'ab' > 'a' | 'ab' > 'a' |
| 'ab' = 'ab' | 'ab' = 'ab' |
| 'ac' > 'ab' | 'ac' > 'ab' |
| 'a ' = 'a' | 'a ' > 'a' |
Generally, the results of blank-padded and nonblank-padded comparisons are the same. The last row of the table illustrates the difference between the two comparison semantics.