Generates sort key values. That is, values that can be used to sort character strings based on alternate collation rules.

Syntax

SORTKEY( string-expression
[, { collation-id  
| collation-name[(collation-tailoring-string) ] } ] 
)

Parameters

Returns

BINARY

Remarks

The SORTKEY function generates values that can be used to order results based on predefined sort order behavior. This allows you to work with character sort order behaviors that may not be available from the database collation. The returned value is a binary value that contains coded sort order information for the input string that is retained from the SORTKEY function. For example, you can store the values returned by the SORTKEY function in a column with the source character string. When you want to retrieve the character data in the desired order, the SELECT statement only needs to include an ORDER BY clause on the columns that contain the results of running the SORTKEY function.

The SORTKEY function guarantees that the values it returns for a given set of sort order criteria work for the binary comparisons that are performed on VARBINARY data types.

Generating sortkeys for queries can be expensive. As an alternative for frequently requested sortkeys, consider creating a computed column to hold the sortkey values, and then referencing that column in the ORDER BY clause of the query.

The input of the SORTKEY function can generate up to six bytes of sort order information for each input character. The output of the SORTKEY function is of type VARBINARY and has a maximum length of 1024 bytes.

When specifying UCA for the collation during sort key generation, by default, collation tailorings are accent and case sensitive. For example, when UCA is specified by itself, the default tailoring applied is equivalent to 'UCA(case=UpperFirst;accent=Respect;punct=Primary)'.

If a different tailoring is provided in the second parameter to SORTKEY, those settings override the default settings. For example, the following two statements are equivalent:

SELECT SORTKEY( 'abc', 'UCA(accent=Ignore)' ); 
SELECT SORTKEY( 'abc', 'UCA(case=UpperFirst;accent=Ignore;punct=Primary)' );

When specifying a non-UCA collation, by default, collation tailorings are also accent and case sensitive. However, for non-UCA collations, only the case sensitivity can be overridden using a collation tailoring. For example:

SELECT SORTKEY( 'abc', '1252LATIN1(case=Respect)' );

If the database was created without specifying tailoring options (for example, dbinit -c -zn uca mydb.db), the following two clauses may generate different sort orders, even if the database collation name is specified for the SORTKEY function:

ORDER BY string-expression
ORDER BY SORTKEY( string-expression, database-collation-name )

This is because the default tailoring settings used for database creation and for the SORTKEY function are different. To get the same behavior from SORTKEY as for the database collation, either provide a tailoring syntax for collation-tailoring-string that matches the settings for the database collation, or specify db_collation for collation-name. For example:

SORTKEY( expression, 'db_collation' )

See also

• sort_collation option [database]
• COMPARE function [String]
• International languages and character sets
• String functions

Standards and compatibility

Example

The following statements queries the Employees table and returns the FirstName and Surname of all employees, sorted by the sortkey values for the Surname column using the dict collation (Latin-1, English, French, German dictionary).

SELECT Surname, GivenName FROM Employees ORDER BY SORTKEY( Surname, 'dict' );

The following example returns the sortkey value for abc, using the UCA collation and tailoring options.

SELECT SORTKEY( 'abc', 'UCA(locale=es;case=LowerFirst;accent=respect)' );