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.
sortkey (char_expression | uchar_expression) [, {collation_name |
collation_ID}])
is a character-type column name, variable, or constant expression of char, varchar, nchar or nvarchar type.
is a character-type column name, variable, or constant expression of unichar or univarchar type.
is a quoted string or a character variable that specifies the collation to use. Table 2-10 shows the valid values.
is an integer constant or a variable that specifies the collation to use. Table 2-10 shows the valid values.
Shows sorting by European language dicitionary order:
select * from cust_table where cust_name like "TI%" order by (sortkey(cust_name, "dict")
Shows sorting by simplified Chinese phonetic order:
select *from cust_table where cust name like "TI%" order by (sortkey(cust-name, "gbpinyin")
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
Shows sorting by Simplified Chinese phonetic order using pre-existing keys:
select * from cust_table where cust_name like "TI%" order by cust_chinese_sort.
sortkey, a system function, generates values that can be used to order results based on collation behavior. This allows you to work with character collation behaviors beyond the default set of Latin-character-based dictionary sort orders and case or accent sensitivity. The return value is a varbinary datatype value that contains coded collation information for the input string that is returned 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.
sortkey guarantees that the values it returns for a given set of collation criteria work for the binary comparisons that are performed on varbinary datatypes.
sortkey can generate up to 6 bytes of collation information for each input character. Therefore, the result from using sortkey may exceed the length limit of the varbinary datatype. If this happens, the result is truncated to fit. Since this limit is dependent on the logical page size of your server, truncation removes result bytes for each input character until the result string is less than the following for DOL and APL tables:
Locking scheme |
Page size |
Maximum row length |
Maximum column length |
|---|---|---|---|
APL tables |
2K (2048 bytes) |
1962 |
1960 bytes |
4K (4096 bytes) |
4010 |
4008 bytes |
|
8K (8192 bytes) |
8106 |
8104 bytes |
|
16K (16384 bytes) |
16298 |
16296 bytes |
|
DOL tables |
2K (2048 bytes) |
1964 |
1958 bytes |
4K (4096 bytes) |
4012 |
4006 bytes |
|
8K (8192 bytes) |
8108 |
8102 bytes |
|
16K (16384 bytes) |
16300 |
16294 bytes if table does not include any variable length columns |
|
16K (16384 bytes) |
16300 (subject to a max start offset of varlen = 8191) |
8191-6-2 = 8183 bytes if table includes at least on variable length column.* |
|
* This size includes six bytes for the row overhead and two bytes for the row length field |
|||
If this occurs, Adaptive Server issues a warning message, but the query or transaction that contained the sortkey function continues to run.
char_expression or uchar_expression must be composed of characters that are encoded in the server’s default character set.
char_expression or uchar_expression can be an empty string. If it is an empty string:
sortkey returns a zero-length varbinary value, and
stores a blank for the empty string.
An empty string has a different collation value than an NULL string from a database column.
If char_expression or uchar_expression is NULL, sortkey returns a NULL value.
If a unicode expression has no specified sort order, the unicode default sort order is used.
If you do not specify a value for collation_name or collation_ID, sortkey assumes binary collation.
The binary values generated from the sortkey function can change from one major version to another major version of Adaptive Server, such as version 12.0 to 12.5, version 12.9.2 to 12.0, and so on. If you are upgrading to the current version of Adaptive Server, you must regenerate the keys and repopulate the shadow columns before any binary comparison takes place.
Upgrades from version 12.5 to 12.5.0.1 do not require
this step, and Adaptive Server does not generate any errors or warning
messages if you do not regenerate the keys. Although a query involving
the shadow columns should work fine, the comparison result may differ
from pre-upgrade server.
There are two types of collation tables you can use to perform multilingual sorting:
A “built-in” collation table created by the sortkey function. This function exists in versions of higher than Adaptive Server version 11.5.1. You can use either the collation name or the collation ID to specify a built-in table.
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 at $SYBASE/collate/unicode.
Both of these methods work equally well, but a “built-in” table is tied to a Sybase Adaptive Server database, an external table is not. If you use an Adaptive Server database, a built-in table provides the best performance. both of these methods can handle any mix of English, European, and Asian languages.
There are two ways of using sortkey:
In-line: This uses sortkey as part of the order by clause and is useful for retrofitting an existing application and minimizing the changes. Note however, that this method generates sort keys on-the-fly, and therefore does not provide optimum performance on large datasets of over 1000 records.
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 out the list by executing either the stored procedure sp_helpsort, or by querying and selecting the name, id, and description from syscharsets (type is between 2003 and 2999).
Table 2-10 lists the valid values for collation_name and collation_ID.
Description |
Collation name |
Collation ID |
|---|---|---|
Binary sort |
binary |
50 |
Default Unicode multilingual |
default |
0 |
CP 850 Alternative no accent |
altnoacc |
39 |
CP 850 Alternative lower case first |
altdict |
45 |
CP 850 Alternative no case preference |
altnocsp |
46 |
CP 850 Scandinavian dictionary |
scandict |
47 |
CP 850 Scandinavian no case preference |
scannocp |
48 |
GB Pinyin |
gbpinyin |
n/a |
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 Cyrillic dictionary |
cyrdict |
63 |
ISO 8859-5 Russian dictionary |
rusdict |
58 |
ISO 8859-9 Turkish dictionary |
turdict |
72 |
Shift-JIS binary order |
sjisbin |
259 |
Thai dictionary |
thaidict |
1 |
ANSI SQL – Compliance level: Transact-SQL extension.
Any user can execute sortkey.
Functions compare
