Data Type Comparison Rules

Numeric Values

A larger value is greater than a smaller value: -1 is less than 10, and -10 is less than -1.

The floating-point value NaN is greater than any other numeric value and is equal to itself.

Date Values

A later date is considered greater than an earlier one. For example, the date equivalent of '10-AUG-2005' is less than that of '30-AUG-2006', and '30-AUG-2006 1:15 pm' is greater than '30-AUG-2006 10:10 am'.

Character Values

Character values are compared in the following ways:

Binary and Linguistic Sorting

In binary sorting, TimesTen compares character strings according to the concatenated value of the numeric codes of the characters in the database character set. One character is greater than the other if it has a greater numeric values than the other in the character set. Blanks are less than any character.

Linguistic sorting is useful if the binary sequence of numeric codes does not match the linguistic sequence of the characters you are comparing. In linguistic sorting, SQL sorting and comparison are based on the linguistic rule set by NLS_SORT. For more information on linguistic sorts, see Linguistic Sort Rules Support Linguistic Conventions in Oracle TimesTen In-Memory Database Operations Guide.

The default is binary sorting.

Blank-padded and Non-Padded Comparison Semantics

With blank-padded semantics, if two values have different lengths, TimesTen adds blanks to the shorter value until both lengths are equal. Values are then compared character by character up to the first character that differs. The value with the greater character in the first differing position is considered greater. If two values have no differing characters, then they are considered equal. Thus, two values are considered equal if they differ only in the number of trailing blanks.

Blank-padded semantics are used when both values in the comparison are expressions of type CHAR or NCHAR or text literals.

With nonpadded semantics, two values are compared, character by character, up to the first character that differs. The value with the greater character in that position is considered greater. If two values that have differing lengths are identical up to the end of the shorter one, then the longer one is considered greater. If two values of equal length have no differing characters, they are considered equal.

Nonpadded semantics are used when both values in the comparison have the type VARCHAR2 or NVARCHAR2.

An example with blank-padded semantics:

'a   ' = 'a'

An example with nonpadded semantics:

'a   ' > 'a'