NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-54
Considerations—Comparison Predicate
For comparisons between character strings of different lengths, the shorter string is
padded on the right with spaces (HEX 20) until it is the length of the longer string.
A HEX 20 is always used for padding, regardless of whether a single-byte or
double-byte character set is associated with the expression.
Both fixed-length and variable-length strings are padded in this way. For example,
SQL considers the string “JOE” equal to a value JOE stored in a column of data type
CHAR or VARCHAR of width three or more. Similarly, SQL considers a value JOE
stored in any column of the CHAR data type equal to the value JOE stored in any
column of the VARCHAR data type.
Two strings are equal if all characters in the same ordinal position are equal.
Lowercase and uppercase letters are not considered equivalent unless used with a
collation that equivalences them.
SQL determines collations for character comparisons as follows:
°
If neither value is associated with a collation, use binary comparison.
°
If only one value is associated with a collation or if both values are associated
with the same collation, use that collation.
°
If each value is associated with a different collation but one collation is specified
implicitly and one collation is specified explicitly, use the collation that is
specified explicitly.
You cannot compare character values that are implicitly associated with different
collations unless you explicitly specify a collation for the comparison.
You cannot compare character values that are explicitly associated with different
collations. SQL returns an error if you attempt to do so.
•
Comparing numeric data
For comparisons between numbers, decimal-type numbers are converted to binary.
Any exact numeric data type is compatible with all other exact numeric data types.
Floating-point data types are sometimes compatible with exact numeric data types.
Before evaluation, all values in an expression are first converted to the maximum
precision needed anywhere in the expression.
•
Comparing date-time data
You can compare only those date-time values with the same range of datetime fields.
To compare two date-time values with a different range of fields, use the EXTEND
function to expand the values.
For example, to compare a DATETIME DAY TO MINUTE column with a
DATETIME YEAR TO HOUR column, expand both values to the range
DATETIME YEAR TO MINUTE for the comparison, as follows:
EXTEND (DATE1, YEAR TO DAY) > EXTEND (DATE2, YEAR TO DAY)
See EXTEND Function on page E-31 for details about the expansion.
•
Comparing interval data