How the COLLATION() function works in Mariadb?

The COLLATION() function is a string function that returns the name of the collation of a given string. The collation of a string determines how the string is sorted and compared.

Posted on

The COLLATION() function is a string function that returns the name of the collation of a given string. The collation of a string determines how the string is sorted and compared. The COLLATION() function can be used to get the collation information of a string, or to perform various operations based on the collation of a string.

Syntax

The syntax of the COLLATION() function is as follows:

COLLATION(string)

Where:

  • string is an expression that returns a string value to be examined.

The return type of the function is a string value in the utf8 character set.

Examples

Example 1: Getting the collation of a string

In this example, we use the COLLATION() function to get the collation of a string. We use the SELECT statement to display the result.

SELECT COLLATION('Hello') AS collation;

The output is:

+-------------------+
| collation         |
+-------------------+
| latin1_swedish_ci |
+-------------------+

This means that the string ‘Hello’ has the latin1_swedish_ci collation, which is the default collation for the latin1 character set.

Example 2: Getting the collation of a column value

In this example, we use the COLLATION() function to get the collation of a column value. We use the products table as an example, which has the following structure and data:

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50) CHARACTER SET utf8mb4,
  price DECIMAL(10,2),
  description VARCHAR(100)
);

INSERT INTO products VALUES
(1, 'Laptop', 999.99, 'A high-performance laptop'),
(2, 'Mouse', 19.99, 'A wireless mouse'),
(3, 'Keyboard', 49.99, NULL),
(4, 'Monitor', 199.99, 'A 24-inch monitor'),
(5, 'Speaker', 29.99, NULL);

We use the SELECT statement to display the product name and the collation of the name.

SELECT name, COLLATION(name) AS collation
FROM products;

The output is:

+---------+-----------------+
| name    | collation       |
+---------+-----------------+
| Laptop  | utf8mb4_general_ci|
| Mouse   | utf8mb4_general_ci|
| Keyboard| utf8mb4_general_ci|
| Monitor | utf8mb4_general_ci|
| Speaker | utf8mb4_general_ci|
+---------+-----------------+

This means that the name column values have the utf8mb4_general_ci collation, which is the default collation for the utf8mb4 character set.

Example 3: Using the COLLATION() function in a WHERE clause

In this example, we use the COLLATION() function in a WHERE clause to filter the rows based on the collation of a column value. We use the products table as an example, and we want to select only the products that have a name with the utf8mb4_bin collation.

SELECT name, price
FROM products
WHERE COLLATION(name) = 'utf8mb4_bin';

The output is:

+------+-------+
| name | price |
+------+-------+
| No rows returned |
+------+-------+

This means that none of the products have a name with the utf8mb4_bin collation.

Some other functions that are related to the COLLATION() function are:

  • COERCIBILITY(): Returns the collation coercibility value of a given string. Collation coercibility defines how collations will be converted in case of collation conflict, with an expression with higher coercibility being converted to the collation of an expression with lower coercibility. The syntax is COERCIBILITY(string).
  • CONVERT(): Returns a string value converted to a specified character set. The syntax is CONVERT(string USING charset_name).
  • CHARSET(): Returns the name of the character set of a given string. The character set of a string determines how the string is stored and displayed. The syntax is CHARSET(string).
  • COLLATE: Applies a specific collation to a string expression. The syntax is string COLLATE collation_name.

For example, you can use the COLLATE operator to change the collation of a string, which will affect its sorting and comparison:

SELECT 'Hello' COLLATE utf8mb4_bin AS string;

The output is:

+-------+
| string|
+-------+
| Hello |
+-------+

This means that the string ‘Hello’ has the utf8mb4_bin collation, which is a binary collation that compares strings based on their byte values.

Conclusion

The COLLATION() function is a useful function to get the name of the collation of a given string. The collation of a string determines how the string is sorted and compared. The COLLATION() function takes a string expression as an argument, and returns a string value in the utf8 character set that is the name of the collation of the argument. The COLLATION() function can be used to get the collation information of a string, or to perform various operations based on the collation of a string. The COLLATION() function can be used in various contexts, such as in SELECT, UPDATE, DELETE, WHERE, ORDER BY, and GROUP BY clauses. The COLLATION() function can be combined with other functions to perform various collation operations and analyses.