SORTKEY function [String]

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

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

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.

If you do not specify a collation, the default is “thaidict”. Although the following are all valid values for collation name, Sybase IQ is certified only with “thaidict”.

Assume the following table schema:

CREATE TABLE T1(id int, c1 varchar(40) shadowc1 varbinary(240))

SORTKEY() returns values in the sort order thaidict (Thai dictionary), the Thai character set in UTF8 form. The following statements generate the same result:

SELECT c1, SORTKEY(c1) from T1 where rid=3
SELECT c1, SORTKEY(c1, ‘thaidict’) from T1 where rid=3)
SELECT ‘\340\270\201\340\271\207’,SORTKEY(‘\340\279\201\340\271\207’) from T1 where rid=3

The following statements generate the same result. SORTKEY() returns values in the sort order default Unicode multilingual:

SELECT c1, SORTKEY(c1, ‘dict’) from T1 where rid=3
SELECT ‘coop’, SORTKEY(‘coop’, ‘dict’) from T1 where rid=3

The following table shows SORTKEY results using 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 are beyond the limitation of collations supported by Sybase IQ. The returned value is a binary value that contains coded sort order information for the input string retained from the SORTKEY function.

For example, you can issue the following SELECT statement to retrieve 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 could instead store the value returned by SORTKEY in a column with the source character string. When you need to retrieve the character data in the desired order, the SELECT statement needs to include only an ORDER BY clause on the column that contains the results of running SORTKEY.

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.

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.