Watch the 7.3 Webinar On-Demand
This new release brings updates to Universal Storage, query
optimization, and usability that you won’t want to miss.
CAST or CONVERT
{
Info
Both the CAST and CONVERT commands perform the same operation.
Casts the input to the given datatype. 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 | CHAR | DATE | DATETIME[(prec)] |
DECIMAL[(prec [, scale])] | TIME[(prec)] | SIGNED [INTEGER] | UNSIGNED [INTEGER]})
CAST (input AS {BINARY | CHAR | DATE | DATETIME[(prec)] |
DECIMAL[(prec [, scale])] | TIME[(prec)] | SIGNED [INTEGER] | UNSIGNED [INTEGER]})
Return Type
The return type is the datatype specified in the command.
Examples
SELECT CONVERT(-123, UNSIGNED);
****
+-------------------------+
| CONVERT(-123, UNSIGNED) |
+-------------------------+
| 18446744073709551493 |
+-------------------------+
SELECT '2019-01-01', CAST('2019-01-01' AS TIME);
****
+------------+----------------------------+
| 2019-01-01 | CAST('2019-01-01' AS TIME) |
+------------+----------------------------+
| 2019-01-01 | 00:20:19 |
+------------+----------------------------+
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. The data conversion behavior of !:> is the same as for INSERT IGNORE.
{
Info
For a command that uses a casting operator, you can specify any datatype 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 :> DECIMAL(18,2));
****
+----------------------+
| (1 :> DECIMAL(18,2)) |
+----------------------+
| 1.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 type
SELECT (NULL !:> DECIMAL(18,2) NOT NULL);
+-----------------------------------+
| (NULL !:> DECIMAL(18,2) NOT NULL) |
+-----------------------------------+
| 0.00 |
+-----------------------------------+
In the following example, :> operator is used with a string datatype for case-sensitive search:
SELECT * FROM Tabview WHERE element_name = "var" :> VARCHAR (50) COLLATE utf8_bin;
****
+------+--------------+
| id | element_name |
+------+--------------+
| 20 | var |
+------+--------------+
