Categories:

String & Binary Functions

COLLATE

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

This copy can be used in subsequent string comparisons, which will use the new collation_specification.

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 = 'ñ',
       v = 'Ñ',
       COLLATE(v, 'sp-upper'),
       COLLATE(v, 'sp-upper') = 'Ñ'
    FROM collation1;
+---+---------+---------+------------------------+------------------------------+
| V | V = 'Ñ' | V = 'Ñ' | COLLATE(V, 'SP-UPPER') | COLLATE(V, 'SP-UPPER') = 'Ñ' |
|---+---------+---------+------------------------+------------------------------|
| ñ | True    | False   | ñ                      | True                         |
+---+---------+---------+------------------------+------------------------------+

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

SELECT *
    FROM table 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';