MySQL CAST() Function

In MySQL, the CAST() function converts the parameter value of any type to a value of the specified type and return the result.

`CAST()` Syntax

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

CAST(expr AS data_type)

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

Parameters

expr

Required. The value to be converted to a specified data type.
data_type

Required. The target data type. You can use the following data 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.

Return value

The MySQL CAST() function converts a value of any data type to the specified data type and returns the result.

`CAST()` Examples

Here are some examples of CAST().

convert to binary

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

SELECT CAST('hello' AS BINARY);

+--------------------------------------------------+
| CAST('hello' AS 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 regular string as follow:

SELECT BINARY 'hello';

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

Convert to JSON

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

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

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

Convert to datetime

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

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

+----------------------------+--------------------------+-----------------------------------------+
| CAST('2022-02-28' AS DATE) | CAST('10:10:10' AS TIME) | CAST('2022-02-28 10:10:10' AS DATETIME) |
+----------------------------+--------------------------+-----------------------------------------+
| 2022-02-28                 | 10:10:10                 | 2022-02-28 10:10:10                     |
+----------------------------+--------------------------+------------------------------------------

CAST() Syntax