sortkey

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

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

• 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:

  Table 2-10: Maximum row and column length—APL and DOL 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, Adaptive Server uses the binary sort order.

• 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.

Collation tables

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).

• Table 2-11 lists the valid values for collation_name and collation_ID.

  Table 2-11: Collation names and IDs

  Description	Collation name	Collation ID
  Deafult Unicode multilingual	default	20
  Thai dictionary order	thaidict	21
  ISO14651 standard	iso14651	22
  UTF-16 ordering – matches UTF-8 binary ordering	utf8bin	24
  CP 850 Alternative – no accent	altnoacc	39
  CP 850 Alternative – lowercase first	altdict	45
  CP 850 Western European – no case preference	altnocsp	46
  CP 850 Scandinavian – dictionary ordering	scandict	47
  CP 850 Scandinavian – case-insensitive with 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-9 Turkish dictionary	turdict	72
  ISO 8859-9 Turkish no accents	turknoac	73
  ISO 8859-9 Turkish no case	turknocs	74
  CP932 binary ordering	cp932bin	129
  Chinese phonetic ordering	dynix	130
  GB2312 binary ordering	gb2312bn	137
  Common Cyrillic dictionary	cyrdict	140
  Turkish dictionary	turdict	155
  EUCKSC binary ordering	euckscbn	161
  Chinese phonetic ordering	gbpinyin	163
  Russian dictionary ordering	rusdict	165
  SJIS binary ordering	sjisbin	179
  EUCJIS binary ordering	eucjisbn	192
  BIG5 binary ordering	big5bin	194
  Shift-JIS binary order	sjisbin	259

Function compare

---

Copyright © 2009. Sybase Inc. All rights reserved.

View this document as PDF