SORTKEY Function [String]

Generates 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

Parameter	Description
string-expression	The string expression must contain characters that are encoded in the character set of the database and must be STRING data type. 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. There is no limit on the length of the input string that the SORTKEY function can handle. The result of SORTKEY is always limited to 1024 bytes and is VARBINARY data type. If the actual results exceed 1024 bytes, the result contains only the first 1024 bytes.
collation-name	A string or 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 sort-keys as used by the CHAR collation in use by the database. Similarly, you can specify the alias NCHAR_COLLATION to generate sort-keys as used by the NCHAR collation in use by the database. However, Sybase IQ does not support NCHAR_COLLATION for Sybase IQ-specific objects. NCHAR_COLLATION is supported for SQL Anywhere objects on a Sybase IQ server.
collation-id	A variable, integer constant, or string that specifies the ID number of the sort order to use. This parameter applies only to Adaptive Server Enterprise collations, which can be referred to by their corresponding collation ID.
collation-tailoring-string	(Optional) Specify collation tailoring options (`collation-tailoring-string`) for additional control over sorting and comparison 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 COLLATION clause of the CREATE DATABASE statement. . See Reference: Statements and Options > SQL Statements > CREATE DATABASE Statement. Note: All of the collation tailoring options are supported for SQL Anywhere databases, when specifying the Unicode Collation Algorithm (UCA) collation. For all other collations, only case sensitivity tailoring is supported.

Returns

BINARY

Example

The following statement queries the Employees table and returns the FirstName and Surname of all employees, sorted by the sort-key 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' );

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 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. The following SELECT statement retrieves data from table T1 in the sorted order of c1 according to the Thai dictionary:

SELECT rid, c1 from T1 ORDER BY SORTKEY(c1)

You instead store the value returned by SORTKEY in a column with the source character string. To retrieve the character data in the required order, the SELECT statement needs to include only an ORDER BY clause on the column that contains the results of running the SORTKEY function:

UPDATE T1 SET shadowc1=SORTKEY(c1) FROM T1;
SELECT rid, c1 FROM T1 ORDER BY shadowc1

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 sort-keys for queries can be expensive. As an alternative for frequently requested sort-keys, consider creating a computed column to hold the sort-key values, and then referencing that column in the ORDER BY clause of the query.

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

Valid collations are as follows:

To see collations that are supported by Sybase IQ, listed by label, execute iqinit -l.

The Adaptive Server Enterprise collations are listed in the table below.

Description	Collation name	Collation ID
Default Unicode multilingual	default	0
CP 850 Alternative: no accent	altnoacc	39
CP 850 Alternative: lowercase first	altdict	45
CP 850 Western European: no case, preference	altnocsp	46
CP 850 Scandinavian dictionary	scandict	47
CP 850 Scandinavian: no case, preference	scannocp	48
GB Pinyin	gbpinyin	n/a
Binary sort	binary	50
Latin-1 English, French, German dictionary	dict	51
Latin-1 English, French, German no case	nocase	52
Latin-1 English, French, German no case, preference	nocasep	53
Latin-1 English, French, German no accent	noaccent	54
Latin-1 Spanish dictionary	espdict	55
Latin-1 Spanish no case	espnocs	56
Latin-1 Spanish no accent	espnoac	57
ISO 8859-5 Russian dictionary	rusdict	58
ISO 8859-5 Russian no case	rusnocs	59
ISO 8859-5 Cyrillic dictionary	cyrdict	63
ISO 8859-5 Cyrillic no case	cyrnocs	64
ISO 8859-7 Greek dictionary	elldict	65
ISO 8859-2 Hungarian dictionary	hundict	69
ISO 8859-2 Hungarian no accents	hunnoac	70
ISO 8859-2 Hungarian no case	hunnocs	71
ISO 8859-5 Turkish dictionary	turdict	72
ISO 8859-5 Turkish no accents	turnoac	73
ISO 8859-5 Turkish no case	turnocs	74
CP 874 (TIS 620) Royal Thai dictionary	thaidict	1
ISO 14651 ordering standard	14651	22
Shift-JIS binary order	sjisbin	179
Unicode UTF-8 binary sort	utf8bin	24
EUC JIS binary order	eucjisbn	192
GB2312 binary order	gb2312bn	137
CP932 MS binary order	cp932bin	129
Big5 binary order	big5bin	194
EUC KSC binary order	euckscbn	161

With respect to collation tailoring, full sensitivity is generally the intent when creating sort-keys, so when you specify a non-UCA collation, the default tailoring applied is equivalent to case=Respect. For example, the following two statements are equivalent:

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

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

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

If the database was created without specifying tailoring options, 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 )

Different sort orders may be generated, 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' )

Note: Sort-key values created using a version of Sybase IQ earlier than 15.0 do not contain the same values created using version 15.0 and later. This may be a problem for your applications if your pre-15.0 database has sort-key values stored within it, especially if sort-key value comparison is required by your application. Regenerate any sort-key values in your database that were generated using a version of Sybase IQ earlier than 15.0.

Standards and Compatibility

SQL—Vendor extension to ISO/ANSI SQL grammar.