Syntax¶

The COLLATE function can be called as a normal function:

COLLATE(<string_expression>,'<collation_specification>')

The COLLATE function can be used as though it were an infix operator:

<string_expression>COLLATE'<collation_specification>'

Arguments¶

string_expression

The string to copy.

collation_specification

The collation to store with the copy of the string. For more information about collationspecifiers, seeCollation specifications.

Returns¶

Returns a copy of the original string, but with the specifiedcollation_specification property instead of the originalcollation_specification.

Usage notes¶

• Each VARCHAR contains a property that holds the collation specifier to use when comparing that VARCHAR toanother VARCHAR. The COLLATE function copies the string, but applies the new collation specificationrather than the original specification to the copy.

  The string itself is unchanged; only the collation specifier associated with the string is changed.

• When COLLATE is used as an infix operator, thecollation_specification must be a constant string,not a general expression.

Examples¶

The following examples show that calling the COLLATE function returns a copy of the string with a differentcollation specification.

Create a table and insert a row. The collation specification of the value in the inserted row ises(Spanish).

CREATEORREPLACETABLEcollation1(vVARCHARCOLLATE'es');INSERTINTOcollation1(v)VALUES('ñ');

This example shows that the COLLATE function does not change the string. The copied string in the third column islowercase, which is the same as the original string in the first column. However, the collation specificationof the value returned by COLLATE has changed fromes toes-ci.

SELECTv,COLLATION(v),COLLATE(v,'es-ci'),COLLATION(COLLATE(v,'es-ci'))FROMcollation1;

+---+--------------+---------------------+--------------------------------+| V | COLLATION(V) | COLLATE(V, 'ES-CI') | COLLATION(COLLATE(V, 'ES-CI')) ||---+--------------+---------------------+--------------------------------|| ñ | es           | ñ                   | es-ci                          |+---+--------------+---------------------+--------------------------------+

This example shows that although the value returned by COLLATE is still a lowercase string, theci collationspecifier is used when comparing that string to another string:

SELECTv,v='ñ'AS"COMPARISON TO LOWER CASE",v='Ñ'AS"COMPARISON TO UPPER CASE",COLLATE(v,'es-ci'),COLLATE(v,'es-ci')='Ñ'FROMcollation1;

+---+--------------------------+--------------------------+---------------------+---------------------------+| V | COMPARISON TO LOWER CASE | COMPARISON TO UPPER CASE | COLLATE(V, 'ES-CI') | COLLATE(V, 'ES-CI') = 'Ñ' ||---+--------------------------+--------------------------+---------------------+---------------------------|| ñ | True                     | False                    | ñ                   | True                      |+---+--------------------------+--------------------------+---------------------+---------------------------+

This example sorts the results using German collation.

SELECT*FROMt1ORDERBYCOLLATE(col1,'de');

The following two queries return the same result. The first uses COLLATE as a function, while the second usesCOLLATE as an infix operator:

SELECTspanish_phraseFROMcollation_demoORDERBYCOLLATE(spanish_phrase,'utf8');

SELECTspanish_phraseFROMcollation_demoORDERBYspanish_phraseCOLLATE'utf8';