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. 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          |
+--------------------+
| utf8mb4_general_ci |
+--------------------+

This means that the string ‘Hello’ has the utf8mb4_general_ci collation, which is the default collation for the utf8mb4 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:

DROP TABLE IF EXISTS products;
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:

Empty set (0.000 sec)

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

Related Functions

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).

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.