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-9 shows the valid values.
is an integer constant or a variable that specifies the collation to use. Table 2-9 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 preexisting 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. Ro retrieve the character data in the desired order, include in the select statement 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 sixbytes 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:
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, regenerate 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 the 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 Adaptive Server later than 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 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:
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.
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).
Table 2-9 lists the valid values for collation_name and collation_ID.
ANSI SQL – Compliance level: Transact-SQL extension.
Any user can execute sortkey.
Function compare
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
