Introduction to the MaxCompute CAST function-MaxCompute(MaxCompute)-阿里云帮助中心

The MaxCompute CAST function converts the result of an expression to a target data type. This topic describes the command format, parameters, and usage examples of the CAST function.

Command format

cast(<expr> as <type>)

Parameter description

expr: Required. The source data to convert.

type: Required. The target data type. The usage is as follows:

cast(double as bigint): Converts a DOUBLE value to a BIGINT value.
cast(string as bigint): Converts a string to a BIGINT value. If the string represents an integer, it is directly converted. If the string represents a floating-point number or a number in scientific notation, it is first converted to the DOUBLE type and then to the BIGINT type.
cast(string as datetime) or cast(datetime as string): The default date format yyyy-mm-dd hh:mi:ss is used.
Converting between basic data types and JSON types.
The cast function also supports conversions between the JSON type and basic data types, including JSON, STRING, BIGINT, INT, TINYINT, SMALLINT, DOUBLE, FLOAT, BOOLEAN, and SQL-TYPE. The following are example commands:
- cast(json as string): Converts a JSON expression to the STRING type. The JSON expression cannot be an ARRAY or OBJECT.
- cast(string as json): Converts a STRING value to a JSON expression of the STRING type. This differs from `json_parse` and `json_format`. `json_parse` converts a valid JSON string into a JSON value, which can be a JSON OBJECT. In contrast, `cast(string as json)` converts any string into a JSON string.
- cast(null as json): Converts a NULL value to the JSON type.
- cast(json 'null' as ...): `json 'null'` and `null` are converted to SQL `null`.

Return value description

Returns a value of the target data type.
If you run setproject odps.function.strictmode=false, the function returns the numbers that appear before any letters.
If you run setproject odps.function.strictmode=true, an error is returned.
When you convert a value to the Decimal type:
- If you set odps.sql.decimal.tostring.trimzero=true, trailing zeros after the decimal point are removed.
- If you set odps.sql.decimal.tostring.trimzero=false, trailing zeros after the decimal point are kept.
  Important
  The odps.sql.decimal.tostring.trimzero parameter affects both data retrieved from tables and static values.

Usage examples

Example 1: Common usage.

--Returns 1.
select cast('1' as bigint);

Example 2: Convert a STRING value to a BOOLEAN value. If the STRING is empty, false is returned. Otherwise, true is returned.

The STRING is empty.

select cast("" as boolean);
--Returns
+------+
| _c0  |
+------+
| false |
+------+

The STRING is not empty.

select cast("false" as boolean);
--Returns true
+------+
| _c0  |
+------+
| true |
+------+

Example 3: Convert a string to a date.

--Convert a string to a date.
select cast("2022-12-20" as date);
--Returns
+------------+
| _c0        |
+------------+
| 2022-12-20 |
+------------+

--Convert a date string with a time part to a date.
select cast("2022-12-20 00:01:01" as date);
--Returns
+------------+
| _c0        |
+------------+
| NULL       |
+------------+
--To ensure the value is displayed correctly, set the following parameter:
set odps.sql.executionengine.enable.string.to.date.full.format= true;
select cast("2022-12-20 00:01:01" as date);
--Returns
+------------+
| _c0        |
+------------+
| 2022-12-20 |
+------------+

Note

By default, the odps.sql.executionengine.enable.string.to.date.full.format parameter is false. To convert a date string that includes a time part, set this parameter to true.

Example 4 (Incorrect command example): Invalid usage. An exception is thrown if a conversion fails or is not supported. The following command is an example of incorrect usage.
```
select cast('abc' as bigint);
```

Example 5: Example of a scenario in which setproject odps.function.strictmode=false is set.

setprojectodps.function.strictmode=false;
select cast('123abc'as bigint);
--Returns
+------------+
|_c0|
+------------+
|123|
+------------+

Example 6: Example of a scenario in which setproject odps.function.strictmode=true is set.

setprojectodps.function.strictmode=true;
select cast('123abc' as bigint);
--Returns
FAILED:ODPS-0130071:[0,0]Semanticanalysisexception-physicalplangenerationfailed:java.lang.NumberFormatException:ODPS-0123091:Illegaltypecast-Infunctioncast,value'123abc'cannotbecastedfromStringtoBigint.

Example 7: Example of a scenario in which odps.sql.decimal.tostring.trimzero is set.

--Create a table.
create table mf_dot (dcm1 decimal(38,18),
                     dcm2 decimal(38,18));
--Insert data.
insert into table mf_dot values (12.45500BD,12.3400BD);

--When the flag is true or not set.
set odps.sql.decimal.tostring.trimzero=true;
--Remove trailing zeros after the decimal point.
select cast(round(dcm1,3) as string),cast(round(dcm2,3) as string) from mf_dot;
--Return value
+------------+------------+
| _c0        | _c1        |
+------------+------------+
| 12.455     | 12.34      |
+------------+------------+

--When the flag is false.
set odps.sql.decimal.tostring.trimzero=false;
--Keep trailing zeros after the decimal point.
select cast(round(dcm1,3) as string),cast(round(dcm2,3) as string) from mf_dot;
--Return value
+------------+------------+
| _c0        | _c1        |
+------------+------------+
| 12.455     | 12.340     |
+------------+------------+

--This parameter also applies to static values.
set odps.sql.decimal.tostring.trimzero=false;
select cast(round(12345.120BD,3) as string);
--Returns:
+------------+
| _c0        |
+------------+
| 12345.120  |
+------------+

Example 8: Convert between STRING and JSON types.

--Convert JSON to string.
select cast(json '123' as string);
--Returns:
+-----+
| _c0 |
+-----+
| 123 |
+-----+

--Convert JSON to string.
select cast(json '"abc"' as string);
--Returns:
+-----+
| _c0 |
+-----+
| abc |
+-----+

--Convert JSON to string.
select cast(json 'true' as string); 
--Returns:
+-----+
| _c0 |
+-----+
| TRUE |
+-----+

--Convert JSON to string.
select cast(json 'null' as string);
--Returns:
+-----+
| _c0 |
+-----+
| NULL |
+-----+

--Convert string to JSON.
select cast('{"a":2}' as json); 
--Returns:
+-----+
| _c0 |
+-----+
| "{\"a\":2}" |
+-----+


--Incorrect example of converting JSON to string. Converting JSON expressions of the ARRAY or OBJECT type to string is not supported.
select cast(json '{"a":2}' as string);
--An error is returned:
FAILED: ODPS-0123091:Illegal type cast - Unsupported cast from json array/object to string

Example 9: Convert between NUMBER and JSON types.

--Convert JSON to bigint.
select cast(json '123' as bigint);
--Returns:
+------------+
| _c0        |
+------------+
| 123        |
+------------+

--Convert JSON to float.
select cast(json '"1.23"' as float);
--Returns:
+------+
| _c0  |
+------+
| 1.23 |
+------+

--Convert JSON to double.
select cast(json '1.23' as double);
--Returns:
+------------+
| _c0        |
+------------+
| 1.23       |
+------------+

--Convert int to JSON.
select cast(123 as json);
--Returns:
+-----+
| _c0 |
+-----+
| 123 |
+-----+

--Convert float to JSON.
select cast(1.23 as json);
--Returns:
+-----+
| _c0 |
+-----+
| 1.23 |
+-----+

Example 10: Convert between BOOLEAN and JSON types.

--Convert boolean to JSON.
select cast(true as json);
--Returns:
+-----+
| _c0 |
+-----+
| true |
+-----+

--Convert JSON to boolean.
select cast(json 'false' as boolean);
--Returns:
+------+
| _c0  |
+------+
| false |
+------+

--Convert JSON to boolean.
select cast(json '"abc"' as boolean);
--Returns:
+------+
| _c0  |
+------+
| true |
+------+

--ARRAY or OBJECT cannot be converted to boolean.
select cast(json '[1,2]' as boolean);
--An error is returned:
Unsupported cast from json array/object to boolean

Example 11: Convert between NULL and JSON types.

--Convert null to JSON.
select json_type(cast(null as json));
--Returns:
+-----+
| _c0 |
+-----+
| NULL |
+-----+

Related functions

The CAST function is a complex type function. For more information about functions that process complex data types such as ARRAY, MAP, STRUCT, and JSON, see Complex type functions.

The CAST function is also classified as another type of function. For more information about functions for other business scenarios, see Other functions.