CAST functions - AnalyticDB - Alibaba Cloud Documentation Center

AnalyticDB for MySQL allows you to use CAST functions to convert values from one data type to another. This topic describes how to use CAST functions.

Syntax

CAST (expr AS type)

Parameters:

expr: the source data. You must specify this parameter.
type: the destination data type. You must specify this parameter.

CAST AS BOOLEAN

CAST (expr AS BOOLEAN)

Description

Converts the value specified by expr to the BOOLEAN type.

Source data types

DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, or TINYINT
BIGINT
DOUBLE
VARCHAR
JSON

Conversion rules

Source data type	Destination data type	Conversion rule
VARCHAR JSON	BOOLAEN	If the source value is true or 1, a value of 1 is returned. If the source value is false or 0, a value of 0 is returned. If the source value is of a different data type, NULL is returned.
DOUBLE	BOOLAEN	If the source value is a number other than 0.0, a value of 1 is returned. If the source value is 0.0, a value of 0 is returned.
DECIMAL(m,d) FLOAT INT/INTEGER SMALLINT TINYINT BIGINT	BOOLAEN	If the source value is a number other than 0, a value of 1 is returned. If the source value is 0, a value of 0 is returned.

Examples

Example 1

Execute the following statement to convert the value 1 from the INT type to the BOOLEAN type:

SELECT CAST('1' AS BOOLEAN);

The following information is returned:

+----------------------+
| CAST('1' AS BOOLEAN) |
+----------------------+
|                    1 |
+----------------------+

Example 2

Execute the following statement to convert the value a from the VARCHAR type to the BOOLEAN type:

SELECT CAST('a' AS BOOLEAN);

The following information is returned:

NULL

Example 3

Execute the following statement to convert the value 4.3 from the DOUBLE type to BOOLEAN type:

SELECT CAST(4.3 AS BOOLEAN);

The following information is returned:

+----------------------+
| CAST(4.3 AS BOOLEAN) |
+----------------------+
|                    1 |
+----------------------+

Example 4

Execute the following statement to convert the value 5 from the INT type to the BOOLEAN type:

SELECT CAST(5 AS BOOLEAN);

The following information is returned:

+--------------------+
| CAST(5 AS BOOLEAN) |
+--------------------+
|                  1 |
+--------------------+

CAST AS DECIMAL(m,d)|FLOAT|INT|INTEGER|SMALLINT|TINYINT

CAST (expr AS [DECIMAL(m,d)|FLOAT|INT|INTEGER|SMALLINT|TINYINT])

Description

Converts the value specified by expr to the DECIMAL(M,D), FLOAT, INT, INTEGER, SMALLINT, or TINYINT type.

Source data types

DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, or TINYINT
BIGINT
DOUBLE
VARCHAR
JSON

Conversion rules

If the value specified by expr is not within the allowed value range of the destination data type, null is returned during the conversion.
For example, execute the following statement to convert the value 99999999 from the BIGINT type to the SMALLINT type:
```
SELECT CAST(99999999 as SMALLINT);
```
NULL is returned because 99999999 is not within the allowed value range of the SMALLINT type.
```
NULL
```
If you convert a value of a high-precision data type to a low-precision data type, the value is rounded after the conversion.
For example, execute the following statement to convert the value 1.23456789 from the DOUBLE type to the FLOAT type:
```
SELECT CAST(1.23456789 AS FLOAT);
```
The following information is returned:
```
+---------------------------+
| CAST(1.23456789 AS FLOAT) |
+---------------------------+
|                 1.2345679 |
+---------------------------+
```
If you convert a value of the DECIMAL(M,D), DOUBLE, or FLOAT type to the INT/INTEGER, SMALLINT, or TINYINT type, the value is rounded after the conversion.
For example, execute the following statement to convert the value 1.1342 from the FLOAT type to the INT type:
```
SELECT CAST(1.1342 AS INT);
```
The following information is returned:
```
+---------------------+
| CAST(1.1342 AS INT) |
+---------------------+
|                   1 |
+---------------------+
```
If the value specified by expr is of the VARCHAR type and is not a number, NULL is returned when the value is converted to the DECIMAL(M,D) or FLOAT type.
For example, execute the following statement to convert the value China from the VARCHAR type to the DECIMAL(M,D) type:
```
SELECT cast( 'China' AS decimal(5,2));
```
The following information is returned:
```
NULL
```
If the value specified by expr is of the VARCHAR type and is not a number, a value of 0 is returned after the value is converted to the INT/INTEGER, SMALLINT, or TINYINT type.
For example, execute the following statement to convert the value China from the VARCHAR type to the SMALLINT type:
```
SELECT CAST( 'China' AS SMALLINT);
```
The following information is returned:
```
+----------------------------+
| CAST( 'China' AS SMALLINT) |
+----------------------------+
|                          0 |
+----------------------------+
```
If the value specified by expr is of the JSON type and is not a number, an error is returned when the value is converted to the INT/INTEGER, SMALLINT, or TINYINT type.
For example, execute the following statement to convert the value [1,2,3] from the VARCHAR type to the JSON type, and then to the SMALLINT type:
```
SELECT CAST(CAST('[1,2,3]' AS JSON) AS SMALLINT);
```
The following error message is returned:
```
ERROR 1815 (HY000): [20034, 2021091814103119216818804803453190138] : Cannot cast json to smallint
```

Examples

Execute the following statement to convert the value 2001012 from the BIGINT type to the FLOAT type:

SELECT CAST(2001012 AS FLOAT);

The following information is returned:

+-------------------------+
| CAST(2001012 AS FLOAT)  |
+-------------------------+
|               2001012.0 |
+-------------------------+

CAST AS BIGINT

CAST (expr AS BIGINT)

Description

Converts the value specified by expr to the BIGINT type.

Source data types

BOOLEAN
DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, or TINYINT
DOUBLE
DATE, DATETIME, TIMESTAMP, or TIME
VARCHAR

Conversion rules

If the source VARCHAR value is not a number, a value of 0 is returned after the source value is converted to the BIGINT type.
For example, execute the following statement to convert the value a from the VARCHAR type to the BIGINT type:
```
SELECT CAST('a' AS BIGINT);
```
The following information is returned:
```
+---------------------+
| CAST('a' AS BIGINT) |
+---------------------+
|                   0 |
+---------------------+
```
If you convert a value of the FLOAT, DOUBLE, or DECIMAL(M,D), type to the BIGINT type, data is rounded after the conversion.
For example, execute the following statement to convert the value 1.23456789 from the DOUBLE type to the BIGINT type:
```
SELECT CAST(1.23456789 AS BIGINT);
```
The following information is returned:
```
+----------------------------+
| CAST(1.23456789 AS BIGINT) |
+----------------------------+
|                          1 |
+----------------------------+
```
If the source JSON value is not a number, NULL is returned when the source value is converted to the BIGINT type.
For example, execute the following statement to convert the value {} from the JSON type to the BIGINT type:
```
SELECT CAST(JSON'{}'AS BIGINT);
```
The following information is returned:
```
NULL
```

Examples

Execute the following statement to convert the value 2021-09-18 from the DATE type to the BIGINT type:

SELECT CAST(DATE '2021-09-18' AS BIGINT);

The following information is returned:

+-----------------------------------+
| CAST(DATE '2021-09-18' AS BIGINT) |
+-----------------------------------+
|                          20210918 |
+-----------------------------------+

Execute the following statement to convert the value -1 from the JSON type to the BIGINT type:

SELECT CAST(JSON '-1' AS BIGINT);

The following information is returned:

+---------------------------+
| CAST(JSON '-1' AS BIGINT) |
+---------------------------+
|                        -1 |
+---------------------------+

Execute the following statement to convert the value FLOOR(4/5) from the DOUBLE type to the BIGINT type:

SELECT CAST(FLOOR(4/5) as BIGINT);

The following information is returned:

+---------------------------+
|CAST(FLOOR(4/5) as BIGINT) |
+---------------------------+
|                        0  |
+---------------------------+

CAST AS DOUBLE

CAST (expr AS DOUBLE)

Description

Converts the value specified by expr to the DOUBLE type.

Source data types

BOOLEAN
DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, or TINYINT
BIGINT
DATE, DATETIME, TIMESTAMP, or TIME
VARCHAR
JSON

Conversion rules

If the source VARCHAR value is not a number, a value of 0.0 is returned after the source value is converted to the DOUBLE type.
For example, execute the following statement to convert the value China from the VARCHAR type to the DOUBLE type:
```
SELECT CAST( 'China' AS DOUBLE);
```
The following information is returned:
```
+--------------------------+
| CAST( 'China' AS DOUBLE) |
+--------------------------+
|                      0.0 |
+--------------------------+
```
If the source JSON value is not a number, NULL is returned when the source value is converted to the DOUBLE type.
For example, execute the following statement to convert the value {} from the JSON type to the DOUBLE type:
```
SELECT CAST(JSON '{}' AS DOUBLE);
```
The following information is returned:
```
NULL
```

Examples

Execute the following statement to convert the value 2021-09-17 from the DATE type to the DOUBLE type:

SELECT CAST(DATE '2021-09-17' AS DOUBLE);

The following information is returned:

+------------------------------------+
| CAST(DATE '2021-09-17' AS DOUBLE)  |
+------------------------------------+
|                        2.0210917E7 |
+------------------------------------+

CAST AS DATE|DATETIME|TIMESTAMP|TIME

CAST (expr AS DATE|DATETIME|TIMESTAMP|TIME)

Description

Converts the value specified by expr to the DATE, DATETIME, TIMESTAMP, or TIME type.

Source data types

DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, or TINYINT
BIGINT
DOUBLE
DATE, DATETIME, TIMESTAMP, or TIME
VARCHAR
JSON

Conversion rules

If the source VARCHAR or BIGINT value does not conform to the TIME format, NULL is returned after the conversion.
For example, execute the following statement to convert the value a from the VARCHAR type to the TIME type:
```
SELECT CAST('a' AS TIME);
```
A value of NULL is returned as shown in the following result because the source value does not conform to the TIME format:
```
+-------------------+
| CAST('a' AS TIME) |
+-------------------+
| NULL              |
+-------------------+
```
If the destination data type contains a time and a date but the corresponding information is not included in the source data, the following rules apply:
- By default, if a time is not included in the value specified by expr, a value of 00:00:00 is returned after the conversion.
  For example, execute the following statement to convert the value 2001-1-22 from the TIMESTAMP type to the TIME type:
```
SELECT CAST(TIMESTAMP '2001-1-22' AS TIME);
```
  A value of 00:00:00 is returned as shown in the following result. This is because a time is not included in the source data.
```
+-------------------------------------+
| CAST(TIMESTAMP '2001-1-22' AS TIME) |
+-------------------------------------+
| 00:00:00                            |
+-------------------------------------+
```
- By default, if a date is not included in the value specified by expr, the system date when the query is executed on the client is returned after the conversion.
  For example, execute the following statement to convert the value 00:00:00 from the TIME type to the DATE type:
```
SELECT CAST(TIME '00:00:00' AS DATE);
```
  The system date when the query is executed is returned as shown in the following result. This is because a date is not included in the source data.
```
+-------------------------------+
| CAST(TIME '00:00:00' AS DATE) |
+-------------------------------+
| 2021-09-14                    |
+-------------------------------+
```

Examples

Execute the following statement to convert the value 20010122000000 from the BIGINT type to the DATE type:

SELECT CAST(20010122000000 AS DATE);

The following information is returned:

+------------------------------+
| CAST(20010122000000 AS DATE) |
+------------------------------+
| 2001-01-22                   |
+------------------------------+

CAST AS VARBINARY

CAST (expr AS VARBINARY)

Description

Converts the value specified by expr to the VARBINARY type.

Source data types

BOOLEAN
DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, or TINYINT
BIGINT
DOUBLE
DATE, DATETIME, TIMESTAMP, or TIME
VARCHAR
JSON

Examples

Execute the following statement to convert the value CHINA from the VARCHAR type to the VARBINARY type:

SELECT CAST(CAST('CHINA' AS VARBINARY) AS VARCHAR);

The following information is returned:

+--------------------------------------------------------+
| CAST(CAST('CHINA' AS VARBINARY) AS VARCHAR)            |
+--------------------------------------------------------+
| CHINA                                                  |
+--------------------------------------------------------+

CAST AS VARCHAR

CAST (expr AS VARCHAR)

Description

Converts the value specified by expr to the VARCHAR type.

Source data types

BOOLEAN
DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, or TINYINT
BIGINT
DOUBLE
DATE, DATETIME, TIMESTAMP, or TIME
VARBINARY
Note
Only AnalyticDB for MySQL clusters of V3.1.4 or later allow you to convert data from the VARBINARY type to the VARCHAR type by using the CAST function.
MAP
JSON

Examples

Execute the following statement to convert the value 2001-1-22 00:00:00 from the TIMESTAMP type to the VARCHAR type:

SELECT CAST(TIMESTAMP '2001-1-22 00:00:00' AS VARCHAR);

The following information is returned:

+-------------------------------------------------+
| CAST(TIMESTAMP '2001-1-22 00:00:00' AS VARCHAR) |
+-------------------------------------------------+
| 2001-01-22 00:00:00                             |
+-------------------------------------------------+

CAST AS ARRAY

CAST (expr AS ARRAY<element_type>)

Description

Converts the value specified by expr to an array that is of the <element_type> type.

Source data types

expr: VARCHAR or JSON.
<element_type>: TINYINT, SMALLINT, INT/INTEGER, or FLOAT.

Conversion rules

If the source VARCHAR or JSON value does not conform to the ARRAY format, an error is returned during the conversion.

For example, execute the following statement to convert the VARCHAR value {}} to an array of FLOAT-type elements:

SELECT CAST('{}}' AS ARRAY<float>);

The following error message is returned because the source value does not conform to the ARRAY format:

ERROR 1815 (HY000): [30013, 2021091815372119216818804803453204662] : Value cannot be cast to array(real)

Examples

Execute the following statement to convert the JSON value [1,2,3] to an array of INT type elements:

SELECT CAST( JSON '[1,2,3]' AS ARRAY<int>);

The following information is returned:

+-------------------------------------+
| CAST( JSON '[1,2,3]' AS ARRAY<int> |
+-------------------------------------+
| [1,2,3]                             |
+-------------------------------------+

CAST AS MAP

CAST (expr AS MAP<element_type_1,element_type_2>)

Description

Converts the value specified by expr to a map that maps the <element_type_1> type to the <element_type_2> type.

Source data types

expr: VARCHAR.
<element_type_1>: BOOLEAN, DECIMAL, DOUBLE, FLOAT, BIGINT, INT/INTEGER, SMALLINT, TINYINT, or VARCHAR.
<element_type_2>: BOOLEAN, DECIMAL, DOUBLE, FLOAT, BIGINT, INT/INTEGER, SMALLINT, TINYINT, VARCHAR, ARRAY, JSON, or MAP.

Conversion rules

If the source VARCHAR value does not conform to the MAP format, an error is returned during the conversion.

For example, execute the following statement to convert the VARCHAR value [a,b,c] to the MAP type:

SELECT CAST('[a,b,c]' AS MAP<varchar,varchar>);

The following error message is returned:

ERROR 1815 (HY000): [30013, 2021091815562519216818804803453207833] : Value cannot be cast to map(varchar,varchar)

Examples

Execute the following statement to convert the VARCHAR value {"1":"a"} to a map that maps a VARCHAR element to the VARCHAR type:

SELECT CAST('{"1":"a"}' AS MAP<varchar,varchar>);

The following information is returned:

+-------------------------------------------+
| CAST('{"1":"a"}' AS MAP<varchar,varchar>) |
+-------------------------------------------+
| {"1":"a"}                                 |
+-------------------------------------------+

CAST AS JSON

CAST (expr AS JSON)

Description

Converts the value specified by expr to the JSON type.

Source data types

BOOLEAN
DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, or TINYINT
BIGINT
DOUBLE
VARCHAR
ARRAY

Conversion rules

If the source VARCHAR value does not conform to the JSON format, NULL is returned after the conversion.

For example, execute the following statement to convert the value {}} from the VARCHAR type to the JSON type:

SELECT CAST('{}}' AS JSON);

The following information is returned:

NULL

Examples

Execute the following statement to convert the value {} from the VARCHAR type to the JSON type:

SELECT CAST('{}' AS JSON);

The following information is returned:

+--------------------+
| CAST('{}' AS JSON) |
+--------------------+
| {}                 |
+--------------------+

Execute the following statement to convert the value 0 from the BIGINT type to the JSON type:

SELECT CAST( BIGINT '0' AS JSON);

The following information is returned:

+---------------------------+
| CAST( BIGINT '0' AS JSON) |
+---------------------------+
| 0                         |
+---------------------------+