The string expression can only contain characters that are encoded in the database's character set.
A variable or integer constant that specifies the sort order to use. You can only use a collation-id for built-in collations. See SORTKEY function [String].
If you do not specify a collation name or ID, the default is Default Unicode multilingual.
A string or a character variable that specifies the name of the collation to use. You can also specify char_collation
or db_collation (for example, COMPARE( 'abc', 'ABC', 'char_collation' );) to use the database's CHAR collation. Similarly, you can specify nchar_collation to use the database's NCHAR collation.
For a list of valid collation names, see SORTKEY function [String].
Optionally, you can specify collation tailoring options (collation-tailoring-string) for additional control over the character comparison. These options take the form of keyword=value pairs in parentheses,
following the collation name. For example, 'UCA(locale=es;case=LowerFirst;accent=respect)'. The syntax for specifying these options is identical to the syntax defined for the COLLATION clause of the CREATE DATABASE
statement. See Collation tailoring options.
All the collation tailoring options are supported when specifying the UCA collation. For all other collations, only case sensitivity
tailoring option is supported.
The COMPARE function does not equate empty strings and strings containing only spaces, even if the database has blank-padding
enabled. The COMPARE function uses the SORTKEY function to generate collation keys for comparison. Therefore, an empty string,
a string with one space, and a string with two spaces do not compare equally.
If either string-expression-1 or string-expression-2 is NULL, the result is NULL.
The values returned are -1, 0, 1, indicating the result of each comparison. The first comparison results in -1, indicating
that string-expression-2 ('ABC') is less than string-expresssion-1 ('abc'). This is because case sensitivity is set to LowerFirst in the first COMPARE statement.