COLLATION_KEY_BIT scalar function
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).
- 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).
Data type of string-expression | Result data type length |
---|---|
CHAR(n) or VARCHAR(n) | Minimum of 12n bytes and 32 672 bytes |
GRAPHIC(n) or VARGRAPHIC(n) | Minimum of 12n 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
- Example 1: 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')
- Example 2: 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'