SORTKEY function [String]

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

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

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.

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 IQ-specific objects. NCHAR_COLLATION is supported for SQL Anywhere objects on an IQ server.

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 SQL Anywhere, listed by label, execute dbinit -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

‘UCA(locale=es;case=LowerFirst;accent=respect)’

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

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.

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

For information on using collation tailoring and the SORTKEY function with a SQL Anywhere database, see “SQL Functions” in the SQL Anywhere Server – SQL Reference.