DB2 Scalar functions - COLLATION_KEY_BIT

COLLATION_KEY_BIT

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

The schema is SYSIBM.

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 returns a collation key of the UTF-16 string with the substitution character or characters and the warning flag SQLWARN8 in the SQLCA is 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

Data type of string-expression	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 is 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 family names by 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, 'CLDR181_LFR') = 
            COLLATION_KEY_BIT(S.SALES_PERSON, 'CLDR181_LFR')
     WHERE  S.REGION = 'Quebec'