DB2 Version 9.7 for Linux, UNIX, and Windows

COLLATION_KEY_BIT scalar function


>>-COLLATION_KEY_BIT--(--string-expression--,--collation-name--+----------+--)-><
                                                               '-, length-'

The schema is SYSIBM.

The COLLATION_KEY_BIT function returns a VARCHAR FOR BIT DATA string representing the collation key of the string-expression in the specified collation-name.

The results of COLLATION_KEY_BIT for two strings can be binary compared to determine their order within the specified collation-name. For the comparison to be meaningful, the results used must be from the same collation-name.

string-expression: An expression that returns a CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC string for which the collation key should be determined. If string-expression is a CHAR or VARCHAR, the expression must not be FOR BIT DATA (SQLSTATE 429BM).; If string-expression is not in UTF-16, this function performs code page conversion of string-expression to UTF-16. If the result of the code page conversion contains at least one substitution character, this function will return a collation key of the UTF-16 string with the substitution character(s) and the warning flag SQLWARN8 in the SQLCA will be set to 'W'.
collation-name: A character constant that specifies the collation to use when determining the collation key. The value of collation-name is not case sensitive and must be one of the Unicode Collation Algorithm-based collations or language-aware collations for Unicode data (SQLSTATE 42616).
length: An expression that specifies the length attribute of the result in bytes. If specified, length must be an integer between 1 and 32 672 (SQLSTATE 42815).

If a value for length is not specified, the length of the result is determined as follows:

Table 1. Determining the result length
String Argument Data Type	Result Data Type Length
CHAR(`n`) or VARCHAR(`n`)	Minimum of 12`n` bytes and 32 672 bytes
GRAPHIC(`n`) or VARGRAPHIC(`n`)	Minimum of 12`n` bytes and 32 672 bytes

Regardless of whether length is specified or not, if the length of the collation key is longer than the length of the result data type, an error is returned (SQLSTATE 42815). The actual result length of the collation key is approximately six times the length of string-expression after it has been converted to UTF-16.

If string-expression is an empty string, the result is a valid collation key that can have a nonzero length.

If any argument can be null, the result can be null; if any argument is null, the result is the null value.

Examples:

The following query orders employees by their surnames using the language-aware collation for German in code page 923:

   SELECT FIRSTNME, LASTNAME
     FROM EMPLOYEE
     ORDER BY COLLATION_KEY_BIT (LASTNAME, 'SYSTEM_923_DE')

The following query uses a culturally correct comparison to find the departments of employees in the province of Québec:

   SELECT E.WORKDEPT
     FROM EMPLOYEE AS E INNER JOIN SALES AS S
     ON COLLATION_KEY_BIT(E.LASTNAME, 'UCA400R1_LFR') =
       COLLATION_KEY_BIT(S.SALES_PERSON, 'UCA400R1_LFR')
     WHERE S.REGION = 'Quebec'