How CAST() Works in MariaDB

In MariaDB, CAST() is a built in function that converts a value to another data type. It takes a value of one type and returns a value of the specified type.

You provide the value as an argument when you call the function, as well as the type that you’d like it converted to.

CAST() works similar to CONVERT().

Syntax

The syntax goes like this:

CAST(expr AS type)

Where expr is the value to convert, and type is the data type that you want it converted to.

Example

Here’s a simple example:

SELECT CAST(123.45 AS INT);

Result:

+---------------------+
| CAST(123.45 AS INT) |
+---------------------+
|                 123 |
+---------------------+

This cast the value to an integer, and therefore, the decimal places were removed.

Here’s another example:

SELECT CAST('2020-01-01' AS DATETIME);

Result:

+--------------------------------+
| CAST('2020-01-01' AS DATETIME) |
+--------------------------------+
| 2020-01-01 00:00:00            |
+--------------------------------+

In this case, we converted a string to a DATETIME data type.

Conversion Failure

In the previous example, we provided a valid DATE string (or DATE literal). Therefore MariaDB was able to convert the value to a DATETIME data type.

However, here’s what happens when we provide an invalid DATE string:

SELECT CAST('1 Jan, 2020' AS DATE);

Result:

+-----------------------------+
| CAST('1 Jan, 2020' AS DATE) |
+-----------------------------+
| NULL                        |
+-----------------------------+

Here, MariaDB apparently couldn’t figure out how to convert this value and returned NULL.

In this case, we could use another function, such as STR_TO_DATE() to perform such a conversion:

SELECT STR_TO_DATE('1 Jan, 2020', '%e %M, %Y');

Result:

+-----------------------------------------+
| STR_TO_DATE('1 Jan, 2020', '%e %M, %Y') |
+-----------------------------------------+
| 2020-01-01                              |
+-----------------------------------------+

Specify a Character Set

It’s possible to specify a character set to use for the return value when using the CAST() function.

Example:

SELECT 
    COLLATION(123) AS "123",
    COLLATION(CAST(123 AS CHAR CHARACTER SET utf16)) AS "utf16",
    COLLATION(CAST(123 AS CHAR CHARACTER SET latin1)) AS "latin1",
    COLLATION(CAST(123 AS CHAR CHARACTER SET big5)) AS "big5";

Result:

+--------+------------------+-------------------+-----------------+
| 123    | utf16            | latin1            | big5            |
+--------+------------------+-------------------+-----------------+
| binary | utf16_general_ci | latin1_swedish_ci | big5_chinese_ci |
+--------+------------------+-------------------+-----------------+

Here, we used the COLLATION() function to return the collation of each value after it’s been cast to the new data type. Each value uses the default collation for the specified character set.

The initial value is not a string, and so COLLATION() returns binary.

Converting to CHAR without specifying the character set will result in the collation_connection character set collation being used.

Specify a Collation

It’s also possible to specify a collation.

Example:

SELECT 
    COLLATION(123) AS "123",
    COLLATION(CAST(123 AS CHAR CHARACTER SET utf16) COLLATE utf16_icelandic_ci) AS "utf16",
    COLLATION(CAST(123 AS CHAR CHARACTER SET latin1) COLLATE latin1_german2_ci) AS "latin1",
    COLLATION(CAST(123 AS CHAR CHARACTER SET big5) COLLATE big5_chinese_nopad_ci) AS "big5";

Result:

+--------+--------------------+-------------------+-----------------------+
| 123    | utf16              | latin1            | big5                  |
+--------+--------------------+-------------------+-----------------------+
| binary | utf16_icelandic_ci | latin1_german2_ci | big5_chinese_nopad_ci |
+--------+--------------------+-------------------+-----------------------+

In this case, we explicitly specified a collation to use that is not the default collation for the specified character set (although still a valid collation for the character set).

Specifying a collation that’s not valid for the character set results in an error.

Here’s what happens when I switch the first two collations around:

SELECT 
    COLLATION(123) AS "123",
    COLLATION(CAST(123 AS CHAR CHARACTER SET utf16) COLLATE latin1_german2_ci) AS "utf16",
    COLLATION(CAST(123 AS CHAR CHARACTER SET latin1) COLLATE utf16_icelandic_ci) AS "latin1",
    COLLATION(CAST(123 AS CHAR CHARACTER SET big5) COLLATE big5_chinese_nopad_ci) AS "big5";

Result:

ERROR 1253 (42000): COLLATION 'latin1_german2_ci' is not valid for CHARACTER SET 'utf16'

It got to the first one and threw an error, because latin1_german2_ci is not a valid collation for the utf16 character set.

See this list of collations available in MariaDB for a full list of collations and their matching character sets.

Null Arguments

Trying to convert null returns null:

SELECT CAST(null AS DATETIME);

Result:

+------------------------+
| CAST(null AS DATETIME) |
+------------------------+
| NULL                   |
+------------------------+

However, passing null without specifying the new data type results in an error:

SELECT CAST(null);

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1

Missing Argument

Calling CAST() without passing an argument results in an error:

SELECT CAST();

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1