Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 » SQL Anywhere Server - SQL Reference » Using SQL » SQL functions » SQL functions (P-Z)


SORTKEY function [String]

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

SORTKEY( string-expression
[, { collation-id  
| collation-name[(collation-tailoring-string) ] } ] 
  • string-expression   The string expression must contain characters that are encoded in the database's character set.

    If string-expression is an empty string, the SORTKEY function returns a zero-length binary value. If string-expression is NULL, the SORTKEY function returns a NULL value. An empty string has a different sort order value than a NULL string from a database column.

    The maximum length of the string that the SORTKEY function can handle is 254 bytes. Any longer part is ignored.

  • collation-name   A string or a character variable that specifies the name of the sort order to use. You can also specify the alias char_collation, or, equivalently, db_collation, to generate sortkeys as used by the CHAR collation in use by the database. Similarly, you can specify the alias nchar_collation to generate sortkeys as used by the NCHAR collation in use by the database.

  • collation-id   A variable, integer constant, or string that specifies the ID number of the sort order to use. If you do not specify collation-id, the default is Default Unicode multilingual. For a list of valid collations, see Supported and alternate collations, and Recommended character sets and collations.

  • collation-tailoring-string   Optionally, you can specify collation tailoring options (collation-tailoring-string) for additional control over the sorting and comparing of characters. These options take the form of keyword=value pairs assembled 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 is supported.




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' )

Sort key values are dependent on the version of SQL Anywhere used to create the database file. For example, the sort key values created by versions of SQL Anywhere prior to 10.0.0, SQL Anywhere version 10, and SQL Anywhere version 11 are different. This may be an issue for your applications if sort key values created by one version of SQL Anywhere are used in a database created by a different version of SQL Anywhere.

It is recommended that you regenerate the sort key values that are stored in the database when using unload and reload to upgrade your SQL Anywhere software.

See also
Standards and compatibility
  • SQL/2003   Vendor extension.


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)' );