How CONVERT() Works in MariaDB

In MariaDB, CONVERT() 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.

CONVERT() is similar to CAST().

Syntax

The CONVERT() function can be called using ODBC syntax or SQL92 syntax.

ODBC syntax:

CONVERT(expr, type)

SQL92 syntax:

CONVERT(expr USING charset)

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

Using the SQL92 syntax, charset is the character set you’d like it converted to.

Example

Here’s a simple example:

SELECT CONVERT(123.45, INT);

Result:

+----------------------+
| CONVERT(123.45, INT) |
+----------------------+
|                  123 |
+----------------------+

This converted the value to an integer, which removed the decimal places.

Here’s another example:

SELECT CONVERT('2020-01-01', DATETIME);

Result:

+---------------------------------+
| CONVERT('2020-01-01', 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 CONVERT('1 Jan, 2020', DATE);

Result:

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

Here, MariaDB 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

The SQL92 syntax allows us to specify a character set to convert to.

Example:

SELECT CONVERT('ไม้เมือง' USING tis620);

Result:

+--------------------------------------------------+
| CONVERT('ไม้เมือง' USING tis620)                   |
+--------------------------------------------------+
| ไม้เมือง                                           |
+--------------------------------------------------+

Changing the character set will also change the collation to be the default collation for that character set.

The above example doesn’t really demonstrate how the character set/collation has changed.

Fortunately we can use functions such as CHARSET() and COLLATION() to see how the character set and collation has changed:

SELECT 
    CHARSET('ไม้เมือง') AS a,
    COLLATION('ไม้เมือง') AS b,
    CHARSET(CONVERT('ไม้เมือง' USING tis620)) AS c,
    COLLATION(CONVERT('ไม้เมือง' USING tis620)) AS d;

Result:

+------+-----------------+--------+----------------+
| a    | b               | c      | d              |
+------+-----------------+--------+----------------+
| utf8 | utf8_general_ci | tis620 | tis620_thai_ci |
+------+-----------------+--------+----------------+

The first one uses the character set and collation for my connection. The second one uses the character set that we explicitly specified with CONVERT(), as well as the default collation for that 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 CONVERT(null, DATETIME);

Result:

+-------------------------+
| CONVERT(null, DATETIME) |
+-------------------------+
| NULL                    |
+-------------------------+

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

SELECT CONVERT(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 CONVERT() without passing an argument results in a syntax error:

SELECT CONVERT();

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