In MariaDB, COERCIBILITY() is a system function that returns the collation coercibility value of its string argument.

It defines how collations will be converted in the case of collation conflict. In such conflicts, an expression with a higher coercibility is converted to the collation of an expression with a lower coercibility.

Syntax

The syntax goes like this:

COERCIBILITY(str)

Where str is the expression that you’d like to get the coercibility for.

The COERCIBILITY() function returns a value between 0 and 5, as outlined in the following table:

Coercibility	Description	Example
0	Explicit	Value using a COLLATE clause
1	No collation	Concatenated strings using different collations
2	Implicit	Column value
3	Constant	The return value from functions like USER(), VERSION(), etc
4	Coercible	Literal string
5	Ignorable	NULL or derived from NULL

Example

Here’s an example to demonstrate:

SELECT COERCIBILITY('Green');

Result:

+-----------------------+
| COERCIBILITY('Green') |
+-----------------------+
|                     4 |
+-----------------------+

This is a normal string and so the coercibility is 4.

Explicit Collation

Here’s an example of explicitly specifying the collation with the COLLATE clause.:

SELECT COERCIBILITY('Mango' COLLATE utf8_spanish_ci);

Result:

+-----------------------------------------------+
| COERCIBILITY('Mango' COLLATE utf8_spanish_ci) |
+-----------------------------------------------+
|                                             0 |
+-----------------------------------------------+

This returns 0 because I explicitly specified the collation with the COLLATE clause.

Database Column

In this example, I return data from a database column.:

SELECT 
    PetName,
    COERCIBILITY(PetName)
FROM Pets
LIMIT 1;

Result:

+---------+-----------------------+
| PetName | COERCIBILITY(PetName) |
+---------+-----------------------+
| Fluffy  |                     2 |
+---------+-----------------------+

The fact that it’s a database column means that it’s an implicit collation, and therefore we get a coercibility of 2.

Constants

In this example, I get the coercibility of the return value from the inbuilt VERSION() function:

SELECT COERCIBILITY(VERSION());

Result:

+-------------------------+
| COERCIBILITY(VERSION()) |
+-------------------------+
|                       3 |
+-------------------------+

Null Values

Here’s what happens when we pass null:

SELECT COERCIBILITY(null);

Result:

+--------------------+
| COERCIBILITY(null) |
+--------------------+
|                  6 |
+--------------------+

Null values are ignorable, and so we get a coercibility of 6.

No Arguments

Calling COERCIBILITY() without any arguments results in an error:

SELECT COERCIBILITY();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'COERCIBILITY'