MariaDB COERCABILITY() Function

In MariaDB, COERCIBILITY() is a built-in function that returns the collation-enforced value for a given string argument.

MariaDB COERCIBILITY() Syntax

Here is the syntax of the MariaDB COERCIBILITY() function:

COERCIBILITY(val)

Parameters

val

Required. A string value.

If you provide no parameters or the wrong number of parameters, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'COERCIBILITY'.

Return value

The MariaDB COERCIBILITY() function returns the mandatory value of the collation for the specified string.

MariaDB provides 7 mandatory values ​​for collation, from 0 to 6, lower values have higher precedence..

Coercibility Meaning Example
0 explicit collation value with COLLATE clause
1 no collation concatenation of strings with different collations
2 implicit collation column value, stored routine parameter, or local variable
3 system constant for example the return value of USER(), VERSION()
4 Mandatory literal string
5 numerical numeric or temporary value
5 ignorable NULL or an expression derived from NULL

MariaDB COERCIBILITY() Examples

Example 1

For values ​​with the COLLATE clause, the MariaDB COERCIBILITY() function will return 0.

SELECT COERCIBILITY('a' COLLATE utf8mb4_general_ci);

Output:

+----------------------------------------------+
| COERCIBILITY('a' COLLATE utf8mb4_general_ci) |
+----------------------------------------------+
|                                            0 |
+----------------------------------------------+

Example 2

For column values, stored routine parameters, or local variables, MariaDB COERCIBILITY() function will return 2.

SET @val = '1';
SELECT COERCIBILITY(@val);

Output:

+--------------------+
| COERCIBILITY(@val) |
+--------------------+
|                  2 |
+--------------------+

Example 3

For system constants, the MariaDB COERCIBILITY() function will return 3.

SELECT COERCIBILITY(USER()), COERCIBILITY(VERSION());

Output:

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

Example 4

For text strings, the MariaDB COERCIBILITY() function will return 4.

SELECT COERCIBILITY('abc');

Output:

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

Example 5

For numeric values, the MariaDB COERCIBILITY() function will return 5.

SELECT COERCIBILITY(123), COERCIBILITY(1.23);

Output:

+-------------------+--------------------+
| COERCIBILITY(123) | COERCIBILITY(1.23) |
+-------------------+--------------------+
|                 5 |                  5 |
+-------------------+--------------------+

Example 6

For NULL, the MariaDB COERCIBILITY() function will return 6.

SELECT COERCIBILITY(NULL);

Output:

+--------------------+
| COERCIBILITY(NULL) |
+--------------------+
|                  6 |
+--------------------+

Conclusion

In MariaDB, COERCIBILITY() is a system function that returns the collation-enforced value for a given string argument.