CAST

Syntax

CAST(expr AS type)

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.

It isn't, of course, possible to convert the values of every <data type> into the values of every other <data type>. For numbers, the rules are:

• CAST (NULL AS <data type>) and CAST (numeric_source_is_a_null_value AS <data type>) both result in a CAST result of NULL.
• You can CAST an exact numeric source to these targets: exact numeric, approximate numeric, fixed length character string, variable length character string, CLOB and NCLOB. You can also CAST an exact numeric source to an interval target, provided the target contains only one datetime field -- that is, you can CAST an integer to INTERVAL YEAR or to INTERVAL MONTH, but you can't CAST it to INTERVAL YEAR TO MONTH. You can CAST an exact numeric source to a UDT target or a <reference type> target if a user-defined cast exists for this purpose and your current <AuthorizationID> has the EXECUTE Privilege on that user-defined cast.

• You can CAST an approximate numeric source to these targets: exact numeric, approximate numeric, fixed length character string, variable length character string, CLOB and NCLOB. You can also CAST an approximate numeric source to a UDT target or a <reference type> target if a user-defined cast exists for this purpose and your current <AuthorizationID> has the EXECUTE Privilege on that user-defined cast.

When you CAST an exact numeric value or an approximate numeric value to an exact numeric target -- for example: "CAST (25 AS INTEGER)", "CAST (1.47E-5 AS DECIMAL(9,7))" -- or when you CAST an exact numeric value or an approximate numeric value to an approximate numeric target (for example: "CAST (25 AS FLOAT)", "CAST (1.47E-5 AS DOUBLE PRECISION)" -- your DBMS checks whether the source is a valid value for the target's <data type> (or if a valid value -- one that doesn't lose any leading significant digits -- can be obtained from the source by rounding or truncation). If so, then the source is converted to that target value. If neither of these are true, the CAST will fail: your DBMS will return the SQLSTATE error 22003 "data exception-numeric value out of range".

[NON-PORTABLE] If your source value is not a valid value for your target <data type>, then the value CAST is non-standard because the SQL Standard requires implementors to define whether the DBMS will round or will truncate the source to obtain a valid target value. [OCELOT Implementation] The OCELOT DBMS that comes with this book truncates the source to obtain a valid target value.

When you CAST an exact numeric value or an approximate numeric value to a fixed length character string target, your DBMS converts the number to the shortest string that represents that number, i.e.,

If the length of the result equals the fixed length of the target, then the source is CAST to that result. If the length of the result is shorter than the fixed length of the target, then the source is CAST to that result, padded on the right with however many spaces is required to make the lengths the same. If the length of the result is longer than the fixed length of the target, the CAST will fail: your DBMS will return the SQLSTATE error 22001 "data exception-string data, right truncation". And if the result contains any characters that don't belong to the target's Character set, the CAST will also fail: your DBMS will return the SQLSTATE error 22018 "data exception-invalid character value for cast".

(Note: if your approximate numeric source value is zero, the CAST result is this character string: '0E0'.)

When you CAST an exact numeric value or an approximate numeric value to a variable length character string target or a CLOB or NCLOB target, your DBMS converts the number to the shortest string that represents that number -- as with fixed length target, it strips off leading plus signs, leading zeros, and any insignificant decimal signs and trailing zeros. If the length of the result is less than or equals the maximum length of the target, then the source is CAST to that result. If the length of the result is longer than the maximum length of the target, the CAST will fail: your DBMS will return the SQLSTATE error 22001 "data exception-string data, right truncation". And if the result contains any characters that don't belong to the target's Character set, the CAST will also fail: your DBMS will return the SQLSTATE error 22018 "data exception-invalid character value for cast".

[Obscure Rule] The result of a CAST to a character string target has the COERCIBLE coercibility attribute; its Collation is the default Collation for the target's Character set.

When you CAST an exact numeric value to an interval target, your DBMS converts it to the value of the interval's single datetime field represented by that number -- for example, "CAST (25 AS INTERVAL YEAR)" results in an interval of 25 years. If the number you're casting is too large for the precision of the target -- as in "CAST (500 AS INTERVAL HOUR(2)" -- the CAST will fail: your DBMS will return the SQLSTATE error 22015 "data exception-interval field overflow".

When you CAST an exact numeric value or an approximate numeric value to a UDT or a <reference type> target, your DBMS invokes the user defined cast routine, with the source value as the routine's argument. The CAST result is the value returned by the user defined cast.

If you want to restrict your code to Core SQL, don't use <Domain name> as a CAST target: CAST only to a <data type>.

Examples

Simple casts:

SELECT CAST("abc" as binary);
SELECT CAST("1" as unsigned integer);
SELECT CAST(123 as char character set utf8)

Note that when one casts to CHAR without specifying the character set, the collation_connection character set collation will be used. When used with CHAR CHARACTER SET, the default collation for that character set will be used.

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

If you also want to change the collation, you have to use the COLLATE operator:

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

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'

See Also

• Supported data types
• Microseconds in MariaDB
• String literals
• COLLATION()
• CONVERT()