ASA SQL Reference
SQL Functions
Alphabetical list of functions
Generates values that can be used to sort character strings based on alternate collation rules.
SORTKEY ( string-expression
[, collation-name | , collation-id ] )
string-expression The string expression may only contain characters that are encoded in the database's character set.
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.
collation-name A string or a character variable that specifies the name of the sort order to use.
collation-id A variable, integer constant, or string that specifies the ID number of the sort order to use.
If you do not specify a collation, the default is Default Unicode multilingual.
Following are the valid values for collation-name and collation-id:
Description | Collation name | Collation ID |
---|---|---|
Default Unicode multilingual | default | 0 |
CP 850 Alternative: no accent | altnoacc | 39 |
CP 850 Alternative: lower case 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 | 257 |
ISO 14651 ordering standard | 14651 | 258 |
Shift-JIS binary order | sjisbin | 259 |
Unicode UTF-8 binary sort | utf8bin | 260 |
EUC JIS binary order | eucjisbin | 261 |
GB2312 binary order | gb2312bin | 262 |
CP932 MS binary order | cp932bin | 263 |
Big5 binary order | big5bin | 264 |
EUC KSC binary order | euckscbin | 265 |
There are two types of collation tables: built-in and external. Built-in tables are included in the DLL, and external tables reside in separate files. You cannot use a collation-id for external tables.
You can also define your own collation tables. To do this, create your own collation table in a .ust file and copy it to the same folder as the pre-installed .ust files, .../charsets/unicode/. You can use the file name as the collation-name.
For more information about .ust files, see the Adaptive Server Enterprise documentation.
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 Adaptive Server Anywhere collations. The returned value is a binary value that contains coded sort order information for the input string is retained 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.
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.
SQL/92 Vendor extension.
SQL/99 Vendor extension.
Sybase Compatible with Adaptive Server Enterprise, except that Adaptive Server Enterprise does not allow the use of self-defined sort orders.
Introduction to international languages and character sets
The following statements return the sort key values in the sort order: Latin-1, English, French, German dictionary.
SELECT SORTKEY('coop', 'dict')
SORTKEY( 'coop', 'dict' ) |
---|
0x08890997099709b30008000800080008 |
SELECT SORTKEY ( 'Cö-op', 51 )
SORTKEY( 'Cö-op', 51 ) |
---|
0x08890997099709b30020004700020008000800080001fffd002d |