COLLATE() - Determine collation element for national strings

Function group: string function

COLLATE() supplies, for national strings, the collation element in accordance with the Default Unicode Collation Table (DUCET), see the “ Core manual”.

Code points which are not assigned and code points > U+2FFF are ignored. Collation elements extend to comparison level 3; level 4 is ignored.

---

COLLATE ( expression USING [[ catalog .]INFORMATION_SCHEMA.] [ collation , length ])

collation ::= { DUCET_WITH_VARS | DUCET_NO_VARS }

length ::= unsigned_integer

---

expression

National expression.

DUCET_WITH_VARS| DUCET_NO_VARS

Name of the collation (sort sequence) to be used.
In SESAM/SQL all collation names are predefined. These are the names which are also defined in the BS2000 software product SORT for sorting strings.

In the case of DUCET_NO_VARS, the variable collation elements, e.g. blanks, punctuation marks and continuation characters, are ignored.

In the case of DUCET_WITH_VARS, they are taken into account.
The strings U&'cannot' and U&'can not' are sorted in this order with
DUCET_NO_VARS, and in the opposite order with DUCET_WITH_VARS.

The collation can be qualified by a database name and the schema name INFORMATION_SCHEMA, otherwise the INFORMATION_SCHEMA is taken as the predefined database.

length

Maximum length of the collation element where 1 <= length <= 32000.

Length not specified:
The result can have a length length of 32000 bytes, depending on expression.

Result

When expression returns the NULL value, the result is the NULL value.

Otherwise:

The result is the collation element for expression in accordance with the Default Unicode Collation Table (DUCET) with the length n = 4 + 6 * (length of expression in code units), where n <= 32000.

If the length of the collation clement is greater than the specified or maximum length, the function is aborted with SQLSTATE.

Data type: VARCHAR(n)

Examples

Output of a list of customer contacts sorted according to the Default Unicode Collation Table taking into account the variable collation elements:

UNLOAD ONLINE DATA CONTACTS (LNAME,FNAME,TITLE,CONTACT_TEL,POSITION) -
    INTO FILE 'DAT.070.C.DUCETWITHVARS' -
    CSV_FORMAT DELIMITER ';' QUOTE '"' ESCAPE '\' EBCDIC -
    ORDER BY COLLATE(TRANSLATE(LNAME USING EDF041 DEFAULT N'?') -
                     USING DUCET_WITH_VARS,200) -
             ASC, -
             COLLATE(TRANSLATE(FNAME USING EDF041 DEFAULT N'?') -
                     USING DUCET_WITH_VARS,200) -
             ASC

Output of the collation element for a letter:

HEX_OF_VALUE(COLLATE(TRANSLATE ('A' USING EDF041) USING DUCET_NO_VARS))

0E33000020000800