Contents Index SIN function [Numeric] SOUNDEX function [String]

ASA SQL Reference
  SQL Functions
    Alphabetical list of functions

SORTKEY function [String]


Function 

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

Syntax 

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

Parameters 

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.

Usage 

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.

Standards and compatibility 
See also 

COMPARE function [String]

Introduction to international languages and character sets

Example 

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

Contents Index SIN function [Numeric] SOUNDEX function [String]