sortkey

Description

Generates values that can be used to order results based on collation behavior, which allows you to work with character collation behaviors beyond the default set of Latin character-based dictionary sort orders and case- or accent-sensitivity.

Syntax

sortkey(char_expression | uchar_expression)[, {collation_name | 
	collation_ID}])

Parameters

char_expression

is a character-type column name, variable, or constant expression of char, varchar, nchar, or nvarchar type.

uchar_expression

is a character-type column name, variable, or constant expression of unichar or univarchar type.

collation_name

is a quoted string or a character variable that specifies the collation to use. Table 2-11 shows the valid values.

collation_ID

is an integer constant or a variable that specifies the collation to use. Table 2-11 shows the valid values.

Examples

Example 1

Shows sorting by European language dicitionary order:

select * from cust_table where cust_name like "TI%" order by 
    (sortkey(cust_name, "dict")

Example 2

Shows sorting by simplified Chinese phonetic order:

select *from cust_table where cust name like "TI%" order by 
    (sortkey(cust-name, "gbpinyin")

Example 3

Shows sorting by European language dictionary order using the in-line option:

select *from cust_table where cust_name like "TI%" order by cust_french_sort

Example 4

Shows sorting by Simplified Chinese phonetic order using preexisting keys:

select * from cust_table where cust_name like "TI%" order by
    cust_chinese_sort.

Usage


Collation tables

There are two types of collation tables you can use to perform multilingual sorting:

  1. A “built-in” collation table created by the sortkey function. This function exists in versions of Adaptive Server later than 11.5.1. You can use either the collation name or the collation ID to specify a built-in table.

  2. An external collation table that uses the Unilib library sorting functions. You must use the collation name to specify an external table. These files are located in $SYBASE/collate/unicode.

    Both of these methods work equally well, but a “built-in” table is tied to a Adaptive Server database, while an external table is not. If you use an Adaptive Server database, a built-in table provides the best performance. Both methods can handle any mix of English, European, and Asian languages.

There are two ways to use sortkey:

  1. In-line – this uses sortkey as part of the order by clause and is useful for retrofitting an existing application and minimizing the changes. However, this method generates sort keys on-the-fly, and therefore does not provide optimum performance on large data sets of moe than 1000 records.

  2. Pre-existing keys – this method calls sortkey whenever a new record requiring multilingual sorting is added to the table, such as a new customer name. Shadow columns (binary or varbinary type) must be set up in the database, preferably in the same table, one for each desired sort order such as French, Chinese, and so on. When a query requires output to be sorted, the order by clause uses one of the shadow columns. This method produces the best performance since keys are already generated and stored, and are quickly compared only on the basis of their binary values.

You can view a list of available collation rules. Print the list by executing either sp_helpsort, or by querying and selecting the name, id, and description from syscharsets (type is between 2003 and 2999).

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute sortkey.

See also

Function compare