- Categories:
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 byCOLLATE
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';