In MySQL, 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.
We provide the value as an argument when we call the function, as well as the type that we want it converted to.
The CONVERT() function is similar to the CAST() function, which also converts between data types.
Syntax
The CONVERT() function can be called using standard SQL syntax or ODBC syntax.
ODBC syntax:
CONVERT(expr,type)Where expr is the value to convert, type is the data type that you want it converted to.
Standard SQL syntax:
CONVERT(expr USING transcoding_name)This syntax converts data between different character sets. In MySQL, transcoding names are the same as the corresponding character set names. Here, transcoding_name is the character set to convert the value to.
Example
Here’s a simple example that uses the ODBC syntax:
SELECT CONVERT( 123.45, SIGNED INTEGER );Result:
+-----------------------------------+ | CONVERT( 123.45, SIGNED INTEGER ) | +-----------------------------------+ | 123 | +-----------------------------------+
This converted the value to a signed integer, which resulted in the fractional part being removed. Actually, when we do this in MySQL it produces a signed or unsigned BIGINT value.
Note that I used SIGNED INTEGER instead of just INTEGER. That’s because we need to specify whether it’s a signed integer or unsigned integer. If we fail to do this, we’ll get an error.
This behaviour is slightly different to MariaDB, where INTEGER is short for SIGNED INTEGER, but we also have the option of explicitly stating SIGNED or UNSIGNED.
Here’s another example that converts a value to a different data type:
SELECT CONVERT('2033-11-08', DATETIME);Result:
+---------------------------------+
| CONVERT('2033-11-08', DATETIME) |
+---------------------------------+
| 2033-11-08 00:00:00 |
+---------------------------------+
In this case, we converted a string to a DATETIME data type, and the result was that a time portion was added and set to 00:00:00.
Conversion Failure
In the previous example, we provided a valid DATE string (or DATE literal). Therefore MySQL 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('20 Dec, 2020', DATE);Result:
+-------------------------------+
| CONVERT('20 Dec, 2020', DATE) |
+-------------------------------+
| NULL |
+-------------------------------+
1 row in set, 1 warning (0.00 sec)
Here, MySQL couldn’t figure out how to convert this value and returned NULL with a warning.
Let’s take a look at the warning:
SHOW WARNINGS;Result:
+---------+------+------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------+ | Warning | 1292 | Incorrect datetime value: '20 Dec, 2020' | +---------+------+------------------------------------------+
As expected, it tells us that we provided an incorrect datetime value.
In this case, we could use another function, such as STR_TO_DATE() to perform such a conversion:
SELECT STR_TO_DATE('20 Dec, 2020', '%e %M, %Y');Result:
+------------------------------------------+
| STR_TO_DATE('20 Dec, 2020', '%e %M, %Y') |
+------------------------------------------+
| 2020-12-20 |
+------------------------------------------+
The result of a conversion failure will depend on the value we’re trying to convert and the data type we’re converting to. For example, here’s what happens when I unsuccessfully try to convert a string to a signed integer:
SELECT CONVERT('ten', SIGNED INT);Result:
+----------------------------+
| CONVERT('ten', SIGNED INT) |
+----------------------------+
| 0 |
+----------------------------+
1 row in set, 1 warning (0.00 sec)
And check the warning:
SHOW WARNINGS;Result:
+---------+------+------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------+ | Warning | 1292 | Truncated incorrect INTEGER value: 'ten' | +---------+------+------------------------------------------+
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 | +---------+--------------------+--------+----------------+ | utf8mb4 | utf8mb4_0900_ai_ci | tis620 | tis620_thai_ci | +---------+--------------------+--------+----------------+
Here, column a shows the character set for my connection, and b shows the collation. Column c uses the character set that we explicitly specified with CONVERT(), and d shows the default collation for that character set.
See How to Find the Collations Supported by the Server in MySQL for to see how to get the collations and their matching character sets. Also check out How to Return a List of Available Character Sets in MySQL for a full list of character sets and their default collations.
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 MySQL 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 MySQL server version for the right syntax to use near ')' at line 1