Outdated Version

You are viewing an older version of this section. View current production version.

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          |
+------+--------------+