All Products
Search
Document Center

Character values

Last Updated: Jun 18, 2021

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.