Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Reference » SQL Functions » Alphabetical list of functions

SOAP_HEADER function [SOAP] Next Page

SORTKEY function [String]


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

Syntax

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

Parameters

string-expression    The string expression must contain characters that are encoded in the database's character set.

If string-expression is an empty string, the SORTKEY function returns a zero-length binary value. If string-expression is NULL, the SORTKEY function 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 the SORTKEY function 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. You can also specify the alias char_collation, or, equivalently, db_collation, to generate sortkeys as used by the CHAR collation in use by the database. Similarly, you can specify the alias nchar_collation to generate sortkeys as used by the NCHAR collation in use by the database.

collation-id    A variable, integer constant, or string that specifies the ID number of the sort order to use. This parameter applies only to Adaptive Server Enterprise collations, which can be referred to by their corresponding collation ID.

If you do not specify a collation name or collation ID, the default is Default Unicode multilingual.

Valid collations are as follows:

collation-tailoring-string    Optionally, you can specify collation tailoring options (collation-tailoring-string) for additional control over the sorting and comparing of characters. These options take the form of keyword=value pairs assembled in parentheses, following the collation name. For example, 'UCA(locale=es;case=LowerFirst;accent=respect)'. The syntax for specifying these options is identical to the syntax defined for the COLLATION clause of the CREATE DATABASE statement. See Collation tailoring options.

Note

All of the collation tailoring options are supported when specifying the UCA collation. For all other collations, only case sensitivity tailoring is supported.

Remarks

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 may not be available from the database collation. The returned value is a binary value that contains coded sort order information for the input string that is retained from the SORTKEY function. For example, you can store the values returned by the SORTKEY function 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 the SORTKEY function.

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.

Generating sortkeys for queries can be expensive. As an alternative for frequently requested sortkeys, consider creating a computed column to hold the sortkey values, and then referencing that column in the ORDER BY clause of the query.

The input of the SORTKEY function can generate up to six bytes of sort order information for each input character. The output of the SORTKEY function is of type VARBINARY and has a maximum length of 1024 bytes.

With respect to collation tailoring, full sensitivity is generally the intent when creating sortkeys so when specifying a non-UCA collation, the default tailoring applied is equivalent to case=Respect. For example, the following two statements are equivalent:

SELECT SORTKEY( 'abc', '1252LATIN1' );
SELECT SORTKEY( 'abc', '1252LATIN1(case=Respect)' );

If UCA is specified by itself, the default tailoring applied is equivalent to 'UCA(case=UpperFirst;accent=Respect;punct=Primary)'.

If a different tailoring is provided in the second parameter to SORTKEY, those settings override the default settings. For example, the following two statements are equivalent:

SELECT SORTKEY( 'abc', 'UCA(accent=Ignore)' ); 
SELECT SORTKEY( 'abc', 'UCA(case=UpperFirst;accent=Ignore;punct=Primary)' );

If the database was created without specifying tailoring options (for example, dbinit -c -zn uca mydb.db), the following two clauses may generate different sort orders, even if the database collation name is specified for the SORTKEY function:

ORDER BY string-expression
ORDER BY SORTKEY( string-expression, database-collation-name )

This is because the default tailoring settings used for database creation and for the SORTKEY function are different. To get the same behavior from SORTKEY as for the database collation, either provide a tailoring syntax for collation-tailoring-string that matches the settings for the database collation, or specify db_collation for collation-name. For example:

SORTKEY( expression, 'db_collation' )
Note

Sort key values created using a version of SQL Anywhere prior to 10.0.0 do not contain the same values created using version 10.0.0 and higher. This may be a problem for your applications if your pre-10.0.0 database had sort key values stored within it, especially if sort key value comparison is required by your application. You should regenerate any sort key values in your database that were generated using a version of SQL Anywhere prior to 10.0.0.

See also
Standards and compatibility
Example

The following statements queries the Employees table and returns the FirstName and Surname of all employees, sorted by the sortkey values for the Surname column using the dict collation (Latin-1, English, French, German dictionary).

SELECT Surname, GivenName FROM Employees ORDER BY SORTKEY( Surname, 'dict' );

The following example returns the sortkey value for abc, using the UCA collation and tailoring options.

SELECT SORTKEY( 'abc', 'UCA(locale=es;case=LowerFirst;accent=respect)' );