MySQL CONVERT() Function

In MySQL, the CONVERT() function converts the parameter value of any type to the specified type or character set.

`CONVERT()` Syntax

Here is the syntax of the MySQL CONVERT() function:

CONVERT(expr, data_type)

CONVERT(expr USING charset)

CONVERT(expr, data_type) is equivalent to CAST(expr AS data_type).

Parameters

expr

Required. The value to be converted.
data_type

Required. The target data type. You can use the following types:
- BINARY[(N)]: If the argument is empty (zero length), the result is of BINARY(0) data type, otherwise the result is a string of VARBINARY data type.
- CHAR[(N)]: The result is a string of VARCHAR data type. Unless the argument is empty, the result is CHAR(0) data type.
- DATE: The result is of DATE data type.
- DATETIME[(M)]: The result is of DATETIME data type and M is the number of fractional seconds.
- DECIMAL[(M[,D])]: The result is of DECIMAL data type.
- DOUBLE: The result is of DOUBLE data type. It was added in MySQL 8.0.17.
- FLOAT[(p)]: Convert the data according to the following rules
  - If not specified p, the result is of FLOAT data type.
  - If 0 <= p <= 24, the result is of FLOAT data type.
  - If 25 <= p <= 53, the result is of DOUBLE data type.
  - If p < 0or p > 53, return an error.
- JSON: The result is of JSON data type.
- NCHAR[(N)]: The result is of NCHAR data type.
- REAL: The result is of REAL data type. Actually, it is FLOAT if REAL_AS_FLOAT enabled, otherwise it is DOUBLE.
- SIGNED [INTEGER]: The result is a signed BIGINT data type.
- TIME[(M)]: The result is of TIME data type and M is the number of fractional seconds.
- UNSIGNED [INTEGER]: The result is an unsigned BIGINT data type.
- YEAR: The result is of YEAR data type, which was added in MySQL 8.0.22.
charset

Required. The character set to convert to.

Return value

The MySQL CONVERT() function converts the parameter value of any type to the specified type or character set and returns the result.

`CONVERT()` Examples

Here are some examples of CONVERT().

Convert to binary

You can use the CONVERT() function to convert a regular string to a binary string.

SELECT CONVERT('hello', BINARY);

+----------------------------------------------------+
| CONVERT('hello', BINARY)                           |
+----------------------------------------------------+
| 0x68656C6C6F                                       |
+----------------------------------------------------+

Note that in the mysql client, binary strings are printed in hexadecimal by default.

You can also use the BINARY operator to get the binary string of a string as follow:

SELECT BINARY 'hello';

+--------------------------------+
| BINARY 'hello'                 |
+--------------------------------+
| 0x68656C6C6F                   |
+--------------------------------+

Convert to JSON

You can use the CONVERT() function to convert a JSON document represented as a string to value of JSON data type.

SELECT CONVERT('[1, 2, "a"]', JSON);

+------------------------------+
| CONVERT('[1, 2, "a"]', JSON) |
+------------------------------+
| [1, 2, "a"]                  |
+------------------------------+

Convert to datetime

You can use the CONVERT() function to convert date/time values represented by strings to data of DATE, DATETIME type.

SELECT
    CONVERT('2022-02-28', DATE),
    CONVERT('10:10:10', TIME),
    CONVERT('2022-02-28 10:10:10', DATETIME);

+-----------------------------+---------------------------+------------------------------------------+
| CONVERT('2022-02-28', DATE) | CONVERT('10:10:10', TIME) | CONVERT('2022-02-28 10:10:10', DATETIME) |
+-----------------------------+---------------------------+------------------------------------------+
| 2022-02-28                  | 10:10:10                  | 2022-02-28 10:10:10                      |
+-----------------------------+---------------------------+------------------------------------------+

Convert character set

This example changes the character set of Hello to the character set latin1:

SELECT CONVERT("Hello" USING latin1);

+-------------------------------+
| CONVERT("Hello" USING latin1) |
+-------------------------------+
| Hello                         |
+-------------------------------+

If the character set used cannot represent the string, there will be some garbled characters in the result. For example:

SELECT
    CONVERT("好" USING latin1),
    CONVERT("好" USING utf8mb4);

+-----------------------------+------------------------------+
| CONVERT("好" USING latin1)  | CONVERT("好" USING utf8mb4)  |
+-----------------------------+------------------------------+
| ?                           | 好                           |
+-----------------------------+------------------------------+

Here, since the character 好 is outside the range of latin1, there is a garbled character ? in output. And converting to utf8mb4 is correct.

CONVERT() Syntax