how to use the cast function to convert data types - AnalyticDB

Syntax

CAST(expr AS type)

Parameter	Required	Description
`expr`	Yes	The source value or expression to convert
`type`	Yes	The destination data type

Supported conversions

The following table lists the destination types supported by CAST and the source types each accepts.

Destination type	Accepted source types
BOOLEAN	DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, TINYINT, BIGINT, DOUBLE, VARCHAR, JSON
DECIMAL(M,D), FLOAT, INT, INTEGER, SMALLINT, TINYINT	DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, TINYINT, BIGINT, DOUBLE, VARCHAR, JSON
BIGINT	BOOLEAN, DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, TINYINT, DOUBLE, DATE, DATETIME, TIMESTAMP, TIME, VARCHAR
DOUBLE	BOOLEAN, DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, TINYINT, BIGINT, DATE, DATETIME, TIMESTAMP, TIME, VARCHAR, JSON
DATE, DATETIME, TIMESTAMP, TIME	DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, TINYINT, BIGINT, DOUBLE, DATE, DATETIME, TIMESTAMP, TIME, VARCHAR, JSON
VARBINARY	BOOLEAN, DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, TINYINT, BIGINT, DOUBLE, DATE, DATETIME, TIMESTAMP, TIME, VARCHAR, JSON
VARCHAR	BOOLEAN, DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, TINYINT, BIGINT, DOUBLE, DATE, DATETIME, TIMESTAMP, TIME, VARBINARY, MAP, JSON
ARRAY\<element_type\>	VARCHAR, JSON
MAP\<key_type, value_type\>	VARCHAR
JSON	BOOLEAN, DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, TINYINT, BIGINT, DOUBLE, VARCHAR, ARRAY

Converting VARBINARY to VARCHAR requires AnalyticDB for MySQL V3.1.4 or later.

Behavior on conversion failure

The result when conversion fails depends on the source and destination types.

Scenario	Result
VARCHAR or BIGINT value does not match the TIME format	NULL
Out-of-range value cast to a numeric type (except INT group)	NULL
Non-numeric VARCHAR cast to DECIMAL(M,D) or FLOAT	NULL
Non-number JSON cast to BIGINT or DOUBLE	NULL
Non-JSON-format VARCHAR cast to JSON	NULL
Out-of-range value cast to INT/INTEGER, SMALLINT, or TINYINT	NULL
Non-numeric VARCHAR cast to INT/INTEGER, SMALLINT, or TINYINT	0
Non-numeric JSON cast to INT/INTEGER, SMALLINT, or TINYINT	Error
Non-ARRAY-format value cast to ARRAY	Error
Non-MAP-format value cast to MAP	Error

CAST AS BOOLEAN

CAST(expr AS BOOLEAN)

Converts expr to BOOLEAN. The result is always 1 (true) or 0 (false).

Conversion rules

Source type	Rule
VARCHAR, JSON	`true` or `1` → `1`; `false` or `0` → `0`; all other values → NULL
DOUBLE	Non-`0.0` → `1`; `0.0` → `0`
DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, TINYINT, BIGINT	Non-zero → `1`; `0` → `0`

Examples

-- Cast VARCHAR '1' to BOOLEAN
SELECT CAST('1' AS BOOLEAN);

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

-- Non-boolean string returns NULL
SELECT CAST('a' AS BOOLEAN);

NULL

-- Non-zero DOUBLE returns 1
SELECT CAST(4.3 AS BOOLEAN);

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

-- Non-zero INT returns 1
SELECT CAST(5 AS BOOLEAN);

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

Converts expr to one of the numeric types: DECIMAL(M,D), FLOAT, INT, INTEGER, SMALLINT, or TINYINT.

Conversion rules

Out-of-range values return NULL. For example, 99999999 exceeds the SMALLINT range:
```
SELECT CAST(99999999 AS SMALLINT);
```
```
NULL
```

High-precision to low-precision conversions round the value:

SELECT CAST(1.23456789 AS FLOAT);

+---------------------------+
| CAST(1.23456789 AS FLOAT) |
+---------------------------+
|                 1.2345679 |
+---------------------------+

DECIMAL(M,D), DOUBLE, or FLOAT to INT/INTEGER, SMALLINT, or TINYINT rounds the value:

SELECT CAST(1.1342 AS INT);

+---------------------+
| CAST(1.1342 AS INT) |
+---------------------+
|                   1 |
+---------------------+

Non-numeric VARCHAR to DECIMAL(M,D) or FLOAT returns NULL:
```
SELECT CAST('China' AS DECIMAL(5,2));
```
```
NULL
```

Non-numeric VARCHAR to INT/INTEGER, SMALLINT, or TINYINT returns 0:

SELECT CAST('China' AS SMALLINT);

+---------------------------+
| CAST('China' AS SMALLINT) |
+---------------------------+
|                         0 |
+---------------------------+

Non-numeric JSON to INT/INTEGER, SMALLINT, or TINYINT returns an error:

SELECT CAST(CAST('[1,2,3]' AS JSON) AS SMALLINT);

ERROR 1815 (HY000): [20034, 2021091814103119216818804803453190138] : Cannot cast json to smallint

Example

SELECT CAST(2001012 AS FLOAT);

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

CAST AS BIGINT

CAST(expr AS BIGINT)

Converts expr to the BIGINT type.

Conversion rules

Non-numeric VARCHAR returns 0:

SELECT CAST('a' AS BIGINT);

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

FLOAT, DOUBLE, or DECIMAL(M,D) values are rounded:

SELECT CAST(1.23456789 AS BIGINT);

+----------------------------+
| CAST(1.23456789 AS BIGINT) |
+----------------------------+
|                          1 |
+----------------------------+

Non-numeric JSON returns NULL:
```
SELECT CAST(JSON'{}' AS BIGINT);
```
```
NULL
```

Examples

-- DATE to BIGINT: returns the date as an integer in YYYYMMDD format
SELECT CAST(DATE '2021-09-18' AS BIGINT);

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

-- JSON numeric value to BIGINT
SELECT CAST(JSON '-1' AS BIGINT);

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

-- Expression result to BIGINT
SELECT CAST(FLOOR(4/5) AS BIGINT);

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

CAST AS DOUBLE

CAST(expr AS DOUBLE)

Converts expr to the DOUBLE type.

Conversion rules

Non-numeric VARCHAR returns 0.0:

SELECT CAST('China' AS DOUBLE);

+--------------------------+
| CAST('China' AS DOUBLE)  |
+--------------------------+
|                      0.0 |
+--------------------------+

Non-numeric JSON returns NULL:
```
SELECT CAST(JSON '{}' AS DOUBLE);
```
```
NULL
```

Example

-- DATE to DOUBLE: returns the date as a floating-point number
SELECT CAST(DATE '2021-09-17' AS DOUBLE);

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

CAST AS DATE|DATETIME|TIMESTAMP|TIME

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

Converts expr to DATE, DATETIME, TIMESTAMP, or TIME.

Conversion rules

VARCHAR or BIGINT that does not match the TIME format returns NULL:

SELECT CAST('a' AS TIME);

+-------------------+
| CAST('a' AS TIME) |
+-------------------+
| NULL              |
+-------------------+

Missing time component defaults to 00:00:00:

SELECT CAST(TIMESTAMP '2001-1-22' AS TIME);

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

Missing date component defaults to the system date when the query runs:

SELECT CAST(TIME '00:00:00' AS DATE);

+-------------------------------+
| CAST(TIME '00:00:00' AS DATE) |
+-------------------------------+
| 2021-09-14                    |
+-------------------------------+

Example

-- BIGINT to DATE
SELECT CAST(20010122000000 AS DATE);

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

CAST AS VARBINARY

CAST(expr AS VARBINARY)

Converts expr to the VARBINARY type. To read the result as a string, wrap with another CAST to VARCHAR.

Example

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

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

CAST AS VARCHAR

CAST(expr AS VARCHAR)

Converts expr to the VARCHAR type.

Converting VARBINARY to VARCHAR requires AnalyticDB for MySQL V3.1.4 or later.

Example

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

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

Converts expr to an array whose elements are of element_type.

Parameter	Accepted values
`expr`	VARCHAR, JSON
`element_type`	TINYINT, SMALLINT, INT/INTEGER, FLOAT

Conversion rules

If expr does not conform to the ARRAY format, an error is returned:

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

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

Example

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

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

CAST AS MAP

CAST(expr AS MAP<key_type, value_type>)

Converts a VARCHAR value to a map from key_type to value_type.

Parameter	Accepted values
`expr`	VARCHAR
`key_type`	BOOLEAN, DECIMAL, DOUBLE, FLOAT, BIGINT, INT/INTEGER, SMALLINT, TINYINT, VARCHAR
`value_type`	BOOLEAN, DECIMAL, DOUBLE, FLOAT, BIGINT, INT/INTEGER, SMALLINT, TINYINT, VARCHAR, ARRAY, JSON, MAP

Conversion rules

If expr does not conform to the MAP format, an error is returned:

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

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

Example

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

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

CAST AS JSON

CAST(expr AS JSON)

Converts expr to the JSON type.

Conversion rules

If expr is a VARCHAR that does not conform to JSON format, NULL is returned:

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

NULL

Examples

-- VARCHAR to JSON
SELECT CAST('{}' AS JSON);

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

-- BIGINT to JSON
SELECT CAST(BIGINT '0' AS JSON);

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