The CAST SQL Function in MySQL and MariaDB - Conversion of values |
|
| | CAST | Syntax: | CAST(Value AS Type) | | Return value: | Depending on the data types used | | Function type: | Conversion function | |
| | The CAST() function converts data to other data types.
The data "type" to convert to can contain the following values:
| BINARY | The length of the output can optionally be specified. |
| CHAR | The length of the character string and the charset can optionally be specified. |
| NCHAR | Like CHAR, but converts to the national character set. Optionally, the length of the character string can be specified. |
| VARCHAR | in Oracle mode, from MariaDB version 10.3. Not available in MySQL 8.0. |
| DATE | |
| DATETIME | Optionally, the number of digits for the output of microseconds can be specified here. The maximum value is 6. |
| TIME | |
| DECIMAL | Optional specification of value size and decimal places: DECIMAL(m[,d]) |
| DOUBLE | |
| FLOAT | Available in MariaDB from version 10.4.5. Present in MySQL 8.0. |
| INTEGER | Equivalent to SIGNED or SIGNED INTEGER. Not available in MySQL 8.0. |
| SIGNED | Or SIGNED INTEGER |
| UNSIGNED | Or UNSIGNED INTEGER |
If the value of "Expression" is invalid for conversion to a numeric value, 0 is returned.
Invalid characters are truncated when converting numeric values from strings.
A preceding invalid character results in the output of 0.
If the value of the argument is NULL, the function returns NULL. | SQL Examples for the CAST function |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
| SELECT cast(100 as binary);
SELECT cast('Test' as binary);
SELECT cast('Test' as binary(2));
SELECT cast(100 as char);
SELECT cast(100 as char(2));
SELECT cast('Test' as integer);
SELECT cast(100 as double);
SELECT cast(100.11 as integer);
SELECT cast(100.11 as decimal);
SELECT cast(100.11 as decimal(10,4));
SELECT cast(-100.11 as unsigned);
SELECT cast('-100.11' as unsigned integer);
SELECT cast('-100.11' as signed);
SELECT cast('100.11Test' as decimal(10,4));
SELECT cast('x100.11Test' as decimal(10,4));
SELECT cast(' -100.11Test' as signed);
SELECT cast('x100.11Test' as signed);
SELECT cast('2023-01-01' as datetime);
SELECT cast('2023-01-01' as datetime(6));
SELECT cast('2023-01-01 14:30:00' as date);
SELECT cast('2023-01-01 14:30:00' as time);
SELECT CHARSET(cast('Test' as char));
SELECT CHARSET(cast('Test' as nchar)); /* To National Charset */
SELECT CHARSET(cast('Test' as char CHARACTER SET utf8mb4));
SELECT cast(null as char);
|
|
| cast(100 as binary) |
|---|
| varchar(3) BINARY |
|---|
| 100 |
|
|
| cast('Test' as binary) |
|---|
| varchar(4) BINARY |
|---|
| Test |
|
|
| cast('Test' as binary(2)) |
|---|
| varchar(2) BINARY |
|---|
| Te |
|
|
| cast(100 as char) |
|---|
| varchar(3) BINARY |
|---|
| 100 |
|
|
| cast(100 as char(2)) |
|---|
| varchar(2) BINARY |
|---|
| 10 |
|
|
| cast('Test' as integer) |
|---|
| int(4) |
|---|
| 0 |
|
|
| cast(100 as double) |
|---|
| double(22) |
|---|
| 100 |
|
|
| cast(100.11 as integer) |
|---|
| int(7) |
|---|
| 100 |
|
|
| cast(100.11 as decimal) |
|---|
| decimal(11) |
|---|
| 100 |
|
|
| cast(100.11 as decimal(10,4)) |
|---|
| decimal(12) |
|---|
| 100.1100 |
|
|
| cast(-100.11 as unsigned) |
|---|
| bigint(20) |
|---|
| 0 |
|
|
| cast('-100.11' as unsigned integer) |
|---|
| bigint(20) |
|---|
| 18446744073709551516 |
|
|
| cast('-100.11' as signed) |
|---|
| int(7) |
|---|
| -100 |
|
|
| cast('100.11Test' as decimal(10,4)) |
|---|
| decimal(12) |
|---|
| 100.1100 |
|
|
| cast('x100.11Test' as decimal(10,4)) |
|---|
| decimal(12) |
|---|
| 0.0000 |
|
|
| cast(' -100.11Test' as signed) |
|---|
| bigint(12) |
|---|
| -100 |
|
|
| cast('x100.11Test' as signed) |
|---|
| bigint(11) |
|---|
| 0 |
|
|
| cast('2023-01-01' as datetime) |
|---|
| datetime(19) |
|---|
| 2023-01-01 00:00:00 |
|
|
| cast('2023-01-01' as datetime(6)) |
|---|
| datetime(26) |
|---|
| 2023-01-01 00:00:00.000000 |
|
|
| cast('2023-01-01 14:30:00' as date) |
|---|
| date(10) |
|---|
| 2023-01-01 |
|
|
| cast('2023-01-01 14:30:00' as time) |
|---|
| time(10) |
|---|
| 14:30:00 |
|
|
| CHARSET(cast('Test' as char)) |
|---|
| varchar(64) BINARY |
|---|
| binary |
|
|
| CHARSET(cast('Test' as nchar)) |
|---|
| varchar(64) BINARY |
|---|
| utf8 |
|
|
| CHARSET(cast('Test' as char CHARACTER SET utf8mb4)) |
|---|
| varchar(64) BINARY |
|---|
| utf8mb4 |
|
|
| cast(null as char) |
|---|
| varchar(0) BINARY |
|---|
| NULL |
|
|
| | The examples were created with the MyWAY SQL manager: | How to use the CAST() function in MySQL and MariaDB databases | In MySQL and MariaDB the CAST() function can be used to convert a value from a data type to another and allows to explicitly specify the target data type for the conversion. The function supports various data types for conversion, including numeric types, string types, date and time types, and more. Commonly used data types for conversion includes among others INTEGER, DECIMAL, VARCHAR, DATE, TIME and DATETIME. Using the function may cause data loss or unexpected results. For example, converting a string to an integer can result in truncation if the string contains non-numeric characters.
The function can be used to ensure that a value is correctly interpreted and processed in a specific data type context, for example to convert a string to a numeric data type for mathematical calculations, or to convert a date and time value to a specific format for comparison or formatting purposes.
Floating point numbers can be converted to an integer or vice versa to perform arithmetic operations or comparisons with different numeric data types.
Strings can be converted to various data types, such as integers or dates, to extract numeric or date and time information from a string.
Values can be converted into compatible data types in order to perform desired operations or comparisons. The function is also useful for formatting the results of a query. For example, numeric values can be represented with formatting options such as decimal precision or leading zeros. | | Further MySQL und MariaDB SQL Conversion functions | |
| | | More information about the CAST SQL function: and and |
|
|
|
|
