COLLATE¶

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 make a copy of.

collation_specification

This specifies the collation to store with the copy of the string. For more information about collation specifiers, see Collation Specifications.

Returns¶

Returns a copy of the original string, but with the specified collation_specification property instead of the original collation_specification.

Usage Notes¶

Each VARCHAR contains a property that holds the collation specifier to use when comparing that VARCHAR to another VARCHAR. The COLLATE function copies the string, but puts the new collation specification rather than the original specification into 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, the collation_specification must be a constant string, not a general expression.

Examples¶

The following example shows that calling the COLLATE function returns a copy of the string with a different collation specification.

Create the table and insert a row. The collation specification of the value in the inserted row is ‘sp’ (Spanish).

CREATE TABLE collation1 (v VARCHAR COLLATE 'sp');
INSERT INTO collation1 (v) VALUES ('ñ');

This shows that the COLLATE function does not change the string. The copied string in the third column is lower case, just like the original string in the first column is lower case. However, the collation specification of the value returned by COLLATE has changed from ‘sp’ to ‘sp-upper’; you can see this in the fourth column.

SELECT v,
       COLLATION(v),
       COLLATE(v, 'sp-upper'),
       COLLATION(COLLATE(v, 'sp-upper'))
    FROM collation1;
+---+--------------+------------------------+-----------------------------------+
| V | COLLATION(V) | COLLATE(V, 'SP-UPPER') | COLLATION(COLLATE(V, 'SP-UPPER')) |
|---+--------------+------------------------+-----------------------------------|
| ñ | sp           | ñ                      | sp-upper                          |
+---+--------------+------------------------+-----------------------------------+

This query shows that although the value returned by COLLATE is still a lower case string, the ‘upper’ collation specifier is used when comparing that string to another string:

SELECT v,
       v = 'ñ' AS "COMPARISON TO LOWER CASE",
       v = 'Ñ' AS "COMPARISON TO UPPER CASE",
       COLLATE(v, 'sp-upper'),
       COLLATE(v, 'sp-upper') = 'Ñ'
    FROM collation1;
+---+--------------------------+--------------------------+------------------------+------------------------------+
| V | COMPARISON TO LOWER CASE | COMPARISON TO UPPER CASE | COLLATE(V, 'SP-UPPER') | COLLATE(V, 'SP-UPPER') = 'Ñ' |
|---+--------------------------+--------------------------+------------------------+------------------------------|
| ñ | True                     | False                    | ñ                      | True                         |
+---+--------------------------+--------------------------+------------------------+------------------------------+

This command sorts the results using German (Deutsch) collation.

SELECT *
    FROM t1
    ORDER BY COLLATE(col1 , 'de');

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

SELECT spanish_phrase FROM collation_demo 
  ORDER BY COLLATE(spanish_phrase, 'utf8');

SELECT spanish_phrase FROM collation_demo 
  ORDER BY spanish_phrase COLLATE 'utf8';