CAST
Syntax
CAST(expr AS type)
Contents
Description
The CAST() function takes a value of one type and produces a value of another type, similar to CONVERT(). See the description of CONVERT() for more information.
The main difference between the CAST() and CONVERT() is that CONVERT(expr,type) is ODBC syntax while CAST(expr as type) and CONVERT(... USING ...) are SQL92 syntax.
MariaDB starting with 5.5.31
Until MariaDB 5.5.31, X'HHHH'
, the standard SQL syntax for binary string literals, erroneously worked in the same way as 0xHHHH
. In 5.5.31 it was intentionally changed to behave as a string in all contexts (and never as a number).
This introduces an incompatibility with previous versions of MariaDB, and all versions of MySQL (see the example below).
Examples
Simple casts:
SELECT CAST("abc" as binary); SELECT CAST("1" as unsigned integer);
Using CAST() to order an ENUM field as a CHAR rather than the internal numerical value:
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 | +------------+
From MariaDB 5.5.31, the following will trigger warnings, since x'aa'
and 'X'aa'
no longer behave as a number. Previously, and in all versions of MySQL, no warnings are triggered since they did erroneously behave as a number:
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'