ASA SQL Reference
SQL Functions
Alphabetical list of functions
Allows you to directly compare two character strings based on alternate collation rules.
COMPARE (
string-expression-1,
string-expression-2
[ , collation-name | , collation-id ] )
string-expression-1 The first string expression.
string-expression-2 The second string expression.
The string expression may only contain characters that are encoded in the database's character set.
collation-name A string or a character variable that specifies the name of the sort order to use. For a list of valid collation names, see SORTKEY function [String].
collation-id A variable or integer constant that specifies the sort order to use. You can only use a collation-id for built-in collations. For more information, see SORTKEY function [String].
If you do not specify a collation name or id, the default is Default Unicode multilingual.
The COMPARE function returns the following values, based on the collation rules that you choose:
Value | Meaning |
---|---|
1 | string-expression-1 is greater than string-expression-2 |
0 | string-expression-1 is equal to string-expression-2 |
-1 | string-expression-1 is less than string-expression-2 |
The COMPARE function does not equate empty strings and strings containing only spaces, even if the database has blank-padding enabled. COMPARE 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 will not compare equally.
If either string-expression-1 or string-expression-2 is null, the result is null.
SQL/92 Vendor extension.
SQL/99 Vendor extension.
Sybase Compatible with Adaptive Server Enterprise.