ASA SQL Reference
  SQL Functions
    Alphabetical list of functions

SORTKEY function [String]

Function 

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

Syntax 

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

Parameters 

string-expression    The string expression may only contain characters that are encoded in the database's character set.

If string-expression is an empty string, SORTKEY returns a zero-length binary value. If string-expression is null, SORTKEY 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 SORTKEY 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.

collation-id    A variable, integer constant, or string that specifies the ID number of the sort order to use.

If you do not specify a collation, the default is Default Unicode multilingual.

Following are the valid values for collation-name and collation-id:

There are two types of collation tables: built-in and external. Built-in tables are included in the DLL, and external tables reside in separate files. You cannot use a collation-id for external tables.

You can also define your own collation tables. To do this, create your own collation table in a .ust file and copy it to the same folder as the pre-installed .ust files, .../charsets/unicode/. You can use the file name as the collation-name.

For more information about .ust files, see the Adaptive Server Enterprise documentation.

Usage 

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 are beyond the limitation of Adaptive Server Anywhere collations. The returned value is a binary value that contains coded sort order information for the input string is retained from the SORTKEY function.

For example, you can store the values returned by SORTKEY 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 SORTKEY.

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.

The input of SORTKEY can generate up to six bytes of sort order information for each input character. The output of SORTKEY is of type VARBINARY and has a maximum length of (254 * 6) bytes.

Standards and compatibility 

• SQL/92    Vendor extension.

• SQL/99    Vendor extension.

• Sybase    Compatible with Adaptive Server Enterprise, except that Adaptive Server Enterprise does not allow the use of self-defined sort orders.

See also 

COMPARE function [String]

Introduction to international languages and character sets

Example 

The following statements return the sort key values in the sort order: Latin-1, English, French, German dictionary.

SELECT SORTKEY('coop', 'dict')

SELECT SORTKEY ( 'Cö-op', 51 )