In MySQL, COLLATION() is a built in function that returns the collation of its string argument.

We provide the string when we call the function.

Syntax

The syntax goes like this:

COLLATION(str)

Where str is the string for which we want the collation of.

Example

Here’s a simple example:

SELECT COLLATION('Sky');

Result:

+--------------------+
| COLLATION('Sky')   |
+--------------------+
| utf8mb4_0900_ai_ci |
+--------------------+

And here’s another example that uses Thai characters:

SELECT COLLATION(_tis620'ไม้เมือง');

Result:

+----------------------------------------------+
| COLLATION(_tis620'ไม้เมือง')                  |
+----------------------------------------------+
| tis620_thai_ci                               |
+----------------------------------------------+

Wrong Argument Type

Passing an argument that is not a string results in the word binary being returned.

SELECT COLLATION(45);

Result:

+---------------+
| COLLATION(45) |
+---------------+
| binary        |
+---------------+

Null Arguments

Passing null results in the word binary being returned.

SELECT COLLATION(null);

Result:

+-----------------+
| COLLATION(null) |
+-----------------+
| binary          |
+-----------------+

Missing Argument

Calling COLLATION() without passing an argument results in an error:

SELECT COLLATION();

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1