COMPARE function [String]

Allows you to compare two character strings based on alternate collation rules.

Syntax

COMPARE( 
string-expression-1,
string-expression-2
[, { collation-id  
| collation-name[(collation-tailoring-string) ] } ] 
)

Parameters

string-expression-1 The first string expression.
string-expression-2 The second string expression.
The string expression can only contain characters that are encoded in the database's character set.
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. See SORTKEY function [String].
If you do not specify a collation name or ID, the default is Default Unicode multilingual.
collation-name 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].
collation-tailoring-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.

Note

All the collation tailoring options are supported when specifying the UCA collation. For all other collations, only case sensitivity tailoring option is supported.

Returns

An INTEGER, 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. 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.

Standards and compatibility

Example

The following example performs three comparisons using the COMPARE function:

SELECT COMPARE( 'abc','ABC','UCA(case=LowerFirst)' ),
       COMPARE( 'abc','ABC','UCA(case=Ignore)' ),
       COMPARE( 'abc','ABC','UCA(case=UpperFirst)' );

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.

Discuss this page in DocCommentXchange.