Convert a value to a specific data type. This function explicitly converts a value from one type to another, such as string to integer.
The CAST() function takes a value of one and produces a value of another type, similar to the function.
The type can be one of the following values:
The main difference between CAST and is that is ODBC syntax while CAST(expr as type) and are SQL92 syntax.
You can use the CAST() function with the INTERVAL keyword.
This introduced an incompatibility with previous versions of MariaDB, and all versions of MySQL (see the example below).
Note that when one casts to without specifying the character set, the character set collation will be used. When used with CHAR CHARACTER SET, the default collation for that character set will be used.
If you also want to change the collation, you have to use the COLLATE operator:
Using CAST() to order an field as a rather than the internal numerical value:
The following will trigger warnings, since x'aa' and 'X'aa' doesn't behave as a number. In all versions of MySQL, no warnings are triggered since they did erroneously behave as a number:
This page is licensed: GPLv2, originally from
CAST(expr AS type)Short for SIGNED INTEGER
SIGNED [INTEGER]
UNSIGNED [INTEGER]
SELECT CAST("abc" AS BINARY);
SELECT CAST("1" AS UNSIGNED INTEGER);
SELECT CAST(123 AS CHAR CHARACTER SET utf8)SELECT COLLATION(CAST(123 AS CHAR));
+------------------------------+
| COLLATION(CAST(123 AS CHAR)) |
+------------------------------+
| latin1_swedish_ci |
+------------------------------+
SELECT COLLATION(CAST(123 AS CHAR CHARACTER SET utf8));
+-------------------------------------------------+
| COLLATION(CAST(123 AS CHAR CHARACTER SET utf8)) |
+-------------------------------------------------+
| utf8_general_ci |
+-------------------------------------------------+SELECT COLLATION(CAST(123 AS CHAR CHARACTER SET utf8)
COLLATE utf8_unicode_ci);
+-------------------------------------------------------------------------+
| COLLATION(CAST(123 AS CHAR CHARACTER SET utf8) COLLATE utf8_unicode_ci) |
+-------------------------------------------------------------------------+
| utf8_unicode_ci |
+-------------------------------------------------------------------------+CREATE TABLE enum_list (enum_field enum('c','a','b'));
INSERT INTO enum_list (enum_field)
VALUES('c'),('a'),('c'),('b');
SELECT * FROM enum_list
ORDER BY enum_field;
+------------+
| enum_field |
+------------+
| c |
| c |
| a |
| b |
+------------+
SELECT * FROM enum_list
ORDER BY CAST(enum_field AS CHAR);
+------------+
| enum_field |
+------------+
| a |
| b |
| c |
| c |
+------------+SELECT CAST(0xAA AS UNSIGNED), CAST(x'aa' AS UNSIGNED), CAST(X'aa' AS UNSIGNED);
+------------------------+-------------------------+-------------------------+
| CAST(0xAA AS UNSIGNED) | CAST(x'aa' AS UNSIGNED) | CAST(X'aa' AS UNSIGNED) |
+------------------------+-------------------------+-------------------------+
| 170 | 0 | 0 |
+------------------------+-------------------------+-------------------------+
1 row in set, 2 warnings (0.00 sec)
Warning (Code 1292): Truncated incorrect INTEGER value: '\xAA'
Warning (Code 1292): Truncated incorrect INTEGER value: '\xAA'SELECT CAST(2019-01-04 AS INTERVAL DAY_SECOND(2)) AS "Cast";
+-------------+
| Cast |
+-------------+
| 00:20:14.00 |
+-------------+