How the COERCIBILITY() function works in Mariadb?

The COERCIBILITY() function is a system function that returns the collation coercibility value of a given string argument. 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 COERCIBILITY() function can be used to get the collation coercibility information of a string, or to perform various operations based on the collation coercibility of a string.

Syntax

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

COERCIBILITY(string)

Where:

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

The return type of the function is an integer value.

Examples

Example 1: Getting the collation coercibility of a string

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

SELECT COERCIBILITY('Hello') AS coercibility;

The output is:

+--------------+
| coercibility |
+--------------+
|            4 |
+--------------+

This means that the string ‘Hello’ has a coercibility of 4, which indicates that it is a coercible literal string.

Example 2: Getting the collation coercibility of a column value

In this example, we use the COERCIBILITY() function to get the collation coercibility 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 coercibility of the name.

SELECT name, COERCIBILITY(name) AS coercibility
FROM products;

The output is:

+----------+--------------+
| name     | coercibility |
+----------+--------------+
| Laptop   |            2 |
| Mouse    |            2 |
| Keyboard |            2 |
| Monitor  |            2 |
| Speaker  |            2 |
+----------+--------------+

This means that the name column values have a coercibility of 2, which indicates that they are implicit strings that are cast to a string data type.

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

In this example, we use the COERCIBILITY() function in a WHERE clause to filter the rows based on the collation coercibility 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 a coercibility of 2.

SELECT name, price
FROM products
WHERE COERCIBILITY(name) = 2;

The output is:

+----------+--------+
| name     | price  |
+----------+--------+
| Laptop   | 999.99 |
| Mouse    |  19.99 |
| Keyboard |  49.99 |
| Monitor  | 199.99 |
| Speaker  |  29.99 |
+----------+--------+

Related Functions

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

• COLLATION(): Returns the name of the collation of a given string. The collation of a string determines how the string is sorted and compared. The syntax is COLLATION(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.

Conclusion

The COERCIBILITY() function is a useful function to get the collation coercibility value of a given string argument. 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 COERCIBILITY() function takes a string expression as an argument, and returns an integer value that is the collation coercibility of the argument. The COERCIBILITY() function can be used to get the collation coercibility information of a string, or to perform various operations based on the collation coercibility of a string. The COERCIBILITY() function can be used in various contexts, such as in SELECT, UPDATE, DELETE, WHERE, ORDER BY, and GROUP BY clauses. The COERCIBILITY() function can be combined with other functions to perform various collation operations and analyses.