CAST or CONVERT
On this page
Note
Both the CAST and CONVERT commands perform the same operation.
Casts the input to the given data type.
There is usually no visible effect on the printed value; there is a change only in the rules for comparison and sorting.
Syntax
CONVERT (input, {BINARY(length) | CHAR(length) | DATE | DATETIME[(prec)] |DECIMAL[(prec [, scale])] | TIME[(prec)] | SIGNED [INTEGER] | UNSIGNED [INTEGER]})CAST (input AS {BINARY(length) | CHAR(length) | DATE | DATETIME[(prec)] |DECIMAL[(prec [, scale])] | TIME[(prec)] | SIGNED [INTEGER] | UNSIGNED [INTEGER]})
Return Type
The return type is the data type specified in the command.
Examples
SELECT CONVERT(-123, UNSIGNED);
+-------------------------+
| CONVERT(-123, UNSIGNED) |
+-------------------------+
| 18446744073709551493 |
+-------------------------+SELECT '2019-01-01', CAST('2019-01-01 16:15:00' AS DATE);
+------------+-------------------------------------+
| 2019-01-01 | CAST('2019-01-01 16:15:00' AS DATE) |
+------------+-------------------------------------+
| 2019-01-01 | 2019-01-01 |
+------------+-------------------------------------+SELECT CAST(123 AS CHAR(2));+-------------------------+
| CAST(123 AS CHAR(2)) |
+-------------------------+
| 12 |
+-------------------------+Cast Operators :> and !:>
Two operators are provided for data type casting - :> for casting with the default error behavior, and !:> which does a "forceful" cast that always succeeds and produces the standard default value for the target data type even if the cast would otherwise produce an error. !:> is the same as for INSERT IGNORE.
Note
For a command that uses a casting operator, you can specify any data type after the casting operator :> or !:> to convert the input value, same as you would specify a column type in a CREATE TABLE statement.
Examples
SELECT (1 :> DOUBLE);
+---------------+
| (1 :> DOUBLE) |
+---------------+
| 1 |
+---------------+
1 row in set (0.015 sec)SELECT (1 :> DECIMAL(18,2));
+----------------------+
| (1 :> DECIMAL(18,2)) |
+----------------------+
| 1.00 |
+----------------------+SELECT '2019-01-01' !:> DATETIME;
+---------------------------+
| '2019-01-01' !:> DATETIME |
+---------------------------+
| 2019-01-01 00:00:00 |
+---------------------------+In the following example, the SELECT command returns an error using :>, while it succeeds and produces a zero value using !:>:
SELECT (NULL :> DECIMAL(18,2) NOT NULL);
ERROR 2222 (HY000): Tried to convert NULL to a NOT NULL typeIn the following example, :> operator is used with a string data type for case-sensitive search:
SELECT * FROM Tabview WHERE element_name = "var" :> VARCHAR (50) COLLATE utf8_bin;
+------+--------------+
| id | element_name |
+------+--------------+
| 20 | var |
+------+--------------+Last modified: September 3, 2024