Character data is compared by using character values. Character values are compared by using the following two measures:
Binary and linguistic comparisons
Blank-padded or nonpadded comparison semantics
Binary and linguistic comparisons
Binary comparison
In the default binary comparison, ApsaraDB for OceanBase compares strings by using the cascaded values of the numeric codes for the characters in the character set of the database. If the numeric value of a character in the character set is larger than the numeric value of another character, the former character is larger. ApsaraDB for OceanBase does not support the American Standard Code for Information Interchange (ASCII) or Extended Binary Coded Decimal Interchange Code (EBCDIC) character set.
Linguistic comparison
In linguistic sorting, both SQL sorting and comparison are performed by using the linguistic rules that are specified by NLS_SORT. If the binary sequence of the character encoding does not match the language sequence that is 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, language comparison is used.
Blank-padded and nonpadded comparison semantics
Blank-padded comparison semantics
If the blank-padded comparison semantics is used and two values are different in length, ApsaraDB for OceanBase first adds blanks to the end of the shorter value. This ensures that the two values are the same in length. ApsaraDB for OceanBase compares the values character by character until the first different character is identified. The value that has a larger character at the first difference position is considered larger. If the two values do not have different characters, they are considered equal. This rule indicates that the two values are equal only if they are different in the number of trailing blanks. Notice
ApsaraDB for OceanBase uses the blank-padded comparison semantics only if both values in the comparison are the values of the CHAR or NCHAR data type, text literals, or the values that are returned by the USER function.
Nonpadded comparison semantics
In the blank-padded comparison semantics, ApsaraDB for OceanBase compares two values character by character until the first different character is identified. The value that has a larger character at the position is considered larger. If the two values in different lengths are the same up to the end of the shorter value, the longer value is considered larger. If the two values whose lengths are equal do not have different characters, the two values are considered equal. Notice
If the data type of one or two values in the comparison is VARCHAR2 or NVARCHAR2, ApsaraDB for OceanBase uses the nonpadded comparison semantics.
Examples
If you compare two character values by using different comparison semantics, the results are different. In the following example, the blank-padded comparison semantics and the nonpadded comparison semantics are used for comparison.
| Blank-padded | Nonpadded |
|---|---|
| 'ac' > 'ab' | 'ac' > 'ab' |
| 'ab' > 'a ' | 'ab' > 'a ' |
| 'ab' > 'a' | 'ab' > 'a' |
| 'ab' = 'ab' | 'ab' = 'ab' |
| 'ac' > 'ab' | 'ac' > 'ab' |
| 'a ' = 'a' | 'a ' > 'a' |
The results of blank-padded and nonpadded comparisons are generally the same. The comparison example of the last row illustrates the difference between the blank-padded comparison semantics and the nonpadded comparison semantics.
