Character data is compared based on the character values. Two measures are supported. The first is binary or linguistic sorting, and the second is blank-padded or nonpadded comparison semantics. This topic describes these two measures that you can use to compare character values.
Binary and linguistic comparisons
Binary comparison
In OceanBase Database, binary comparison is used by default. In binary comparison, OceanBase Database compares character strings based on the concatenated values of the numeric codes of the characters in the database character set. If the numeric value of Character A is greater than that of Character B in the database character set, Character A is greater than Character B. OceanBase Database does not support the ASCII or EBCDIC character set.
Linguistic comparison
In linguistic sorting, all SQL sorting and comparison are based on the linguistic rule specified by NLS_SORT. When the binary sequence of numeric codes does not match the linguistic sequence of the characters that you want to compare, linguistic comparison is useful. If NLS_SORT is not set to a BINARY value and NLS_COMP is set to a LINGUISTIC value, linguistic comparison is used.
Blank-padded and nonpadded comparison semantics
Blank-padded comparison semantics
When blank-padded comparison semantics is used to compare two strings that have different lengths, OceanBase Database inserts blanks to the end of the shorter string to pad it to the length of the longer string. Then, OceanBase Database compares the two values character by character until the first different character appears. The value whose first different character is greater is considered greater. If the two values have identical characters, they are considered equal. Therefore, two values are equal if they differ only in the number of trailing blanks.
Notice
OceanBase Database uses blank-padded comparison semantics only when both values in the comparison are either expressions of the CHAR or NCHAR data type, text literals, or values returned by the USER function.
Non-padded comparison semantics
When nonpadded comparison semantics is used, OceanBase Database compares two values character by character until the first different character appears. The value whose first different character is greater is considered greater. If two values of different length are identical up to the end of the shorter one, the longer value is considered greater. If the two values have the same length and identical characters, they are considered equal.
Notice
OceanBase Database uses the nonpadded comparison semantics when one or both values are of the VARCHAR2 or NVARCHAR2 data type.
If you use different comparison semantics to compare two character values, the results may be different. The following table lists the comparison results that are obtained by using the blank-padded comparison semantics and nonpadded comparison semantics.
| Blank-padded comparison semantics | Nonpadded comparison semantics |
|---|---|
| ‘ac’ > ‘ab’ | ‘ac’ > ‘ab’ |
| ‘ab’ > ’a ’ | ‘ab’ > ’a ’ |
| ‘ab’ > ‘a’ | ‘ab’ > ‘a’ |
| ‘ab’ = ‘ab’ | ‘ab’ = ‘ab’ |
| ‘ac’ > ‘ab’ | ‘ac’ > ‘ab’ |
| ’a ’ = ‘a’ | ’a ’ > ‘a’ |
In general, the results of blank-padded comparison semantics and nonpadded comparison semantics are the same. The last comparison in the table shows the differences between the blank-padded and nonpadded comparison semantics.