CAST - MaxCompute - Alibaba Cloud Documentation Center

The CAST function of MaxCompute is a function for data type conversion. This function converts the data type of a data source that is specified by the expr parameter into the data type that is specified by the type parameter. This topic describes the syntax and parameters of the CAST function. This topic also provides examples on how to use the CAST function.

Syntax

cast(<expr> as <type>)

Parameters

expr: required. The data source whose data type you want to convert.
type: required. The data type into which you want to convert the data. Usage:
- cast(double as bigint): converts a value of the DOUBLE type into the BIGINT type.
- cast(string as bigint): converts a value of the STRING type into the BIGINT type. If the string consists of numerals that are expressed in the INTEGER form, the string is converted into the BIGINT type. If the string consists of numerals that are expressed in the FLOAT or EXPONENTIAL form, the string is converted into the DOUBLE type and then into the BIGINT type.
- The default date format yyyy-mm-dd hh:mi:ss is used for cast(string as datetime) and cast(datetime as string).
The CAST function can also convert values between JSON types and the following basic types: STRING, BIGINT, INT, TINYINT, SMALLINT, DOUBLE, FLOAT, BOOLEAN, and SQL-TYPE. Usage:
- cast(json as string): converts a JSON expression into a value of the STRING type. The JSON expression cannot be of the ARRAY or OBJECT type.
- cast(string as json): converts a value of the STRING type into a JSON expression. The JSON expression is of the STRING type. Take note that the CAST function differs from the JSON_PARSE and JSON_FORMAT functions. You can use the JSON_PARSE function to convert only valid JSON strings into JSON data, which can be a JSON object. You can use the CAST function to convert a string into a JSON string that contains values of the STRING type.
- cast(null as json): converts null values into JSON 'null' values.
- cast(json 'null' as ...): converts JSON 'null' values and common null values into SQL null values.

Return value

A value of the specified data type is returned.
If you specify setproject odps.function.strictmode=false, the number that precedes the letter is returned.
If you specify setproject odps.function.strictmode=true, an error is returned.
If you convert a value into the DECIMAL type and set the odps.sql.decimal.tostring.trimzero parameter to true, a decimal with 0s at the end removed is returned. If you convert a value into the DECIMAL type and set odps.sql.decimal.tostring.trimzero to false, a decimal with 0s at the end retained is returned.
Important
The odps.sql.decimal.tostring.trimzero parameter takes effect only when data is read from a table. This parameter does not take effect on static values.

Examples

Example 1: common usage. Sample statement:

-- The return value is 1. 
select cast('1' as bigint);

Example 2: Convert a value of the STRING type into the BOOLEAN type. If the value of the STRING type is an empty string, false is returned. Otherwise, true is returned. Sample statement:
- The value of the STRING type is an empty string.
```
select cast("" as boolean);
-- The return value is false.
+------+
| _c0  |
+------+
| false |
+------+
```
- The value of the STRING type is a non-empty string.
```
select cast("false" as boolean);
-- The return value is true.
+------+
| _c0  |
+------+
| true |
+------+
```

Example 3: Convert a string into a date.

-- Convert a string into a date.
select cast("2022-12-20" as date);
-- The following result is returned:
+------------+
| _c0        |
+------------+
| 2022-12-20 |
+------------+

-- Convert a date string that contains the hour, minute, and second parts into a date.
select cast("2022-12-20 00:01:01" as date);
-- The following result is returned:
+------------+
| _c0        |
+------------+
| NULL       |
+------------+
-- To ensure that a valid date is returned, run the following commands:
set odps.sql.executionengine.enable.string.to.date.full.format= true;
select cast("2022-12-20 00:01:01" as date);
-- The following result is returned:
+------------+
| _c0        |
+------------+
| 2022-12-20 |
+------------+

Note

By default, the odps.sql.executionengine.enable.string.to.date.full.format parameter is set to false. If you want to convert a date string that contains the hour, minute, and second parts, you must set this parameter to true.

Example 4: (Incorrect usage) If a type conversion fails or is not supported, an error is returned. Incorrect sample statement:
```
select cast('abc' as bigint);
```

Example 5: setproject odps.function.strictmode=false is specified.

setprojectodps.function.strictmode=false;
select cast('123abc'as bigint);
-- The following result is returned:
+------------+
|_c0|
+------------+
|123|
+------------+

Example 6: setproject odps.function.strictmode=true is specified.

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

Example 7: The odps.sql.decimal.tostring.trimzero parameter is specified.

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

-- Set the odps.sql.decimal.tostring.trimzero parameter to true, or do not configure the odps.sql.decimal.tostring.trimzero parameter.
set odps.sql.decimal.tostring.trimzero=true;
-- Remove the 0s at the end of the decimal.
select cast(round(dcm1,3) as string),cast(round(dcm2,3) as string) from mf_dot;
-- The following result is returned:
+------------+------------+
| _c0        | _c1        |
+------------+------------+
| 12.455     | 12.34      |
+------------+------------+

-- Set the odps.sql.decimal.tostring.trimzero parameter to false.
set odps.sql.decimal.tostring.trimzero=false;
-- Retain the 0s at the end of the decimal.
select cast(round(dcm1,3) as string),cast(round(dcm2,3) as string) from mf_dot;
-- The following result is returned:
+------------+------------+
| _c0        | _c1        |
+------------+------------+
| 12.455     | 12.340     |
+------------+------------+

-- The odps.sql.decimal.tostring.trimzero parameter does not take effect for static values.
set odps.sql.decimal.tostring.trimzero=false;
select cast(round(12345.120BD,3) as string);
-- The following result is returned:
+------------+
| _c0        |
+------------+
| 12345.12   |
+------------+

Example 8: Convert values between the STRING type and JSON types.

-- Convert a JSON number into a value of the STRING type.
select cast(json '123' as string);
-- The following result is returned:
+-----+
| _c0 |
+-----+
| 123 |
+-----+

-- Convert a JSON string into a value of the STRING type.
select cast(json '"abc"' as string);
-- The following result is returned:
+-----+
| _c0 |
+-----+
| abc |
+-----+

-- Convert a JSON boolean into a value of the STRING type.
select cast(json 'true' as string); 
-- The following result is returned:
+-----+
| _c0 |
+-----+
| TRUE |
+-----+

-- Convert JSON 'null' into a null value of the STRING type.
select cast(json 'null' as string);
-- The following result is returned:
+-----+
| _c0 |
+-----+
| NULL |
+-----+

-- Convert a string into a value of a JSON type.
select cast('{"a":2}' as json); 
-- The following result is returned:
+-----+
| _c0 |
+-----+
| "{\"a\":2}" |
+-----+


-- Incorrect sample statement. Convert JSON expressions of the ARRAY or OBJECT type into values of the STRING type. 
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 values between the NUMBER type and JSON types.

-- Convert a JSON number into a value of the BIGINT type.
select cast(json '123' as bigint);
-- The following result is returned:
+------------+
| _c0        |
+------------+
| 123        |
+------------+

-- Convert a JSON number into a value of the FLOAT type.
select cast(json '"1.23"' as float);
-- The following result is returned:
+------+
| _c0  |
+------+
| 1.23 |
+------+

-- Convert a JSON number into a value of the DOUBLE type.
select cast(json '1.23' as double);
-- The following result is returned:
+------------+
| _c0        |
+------------+
| 1.23       |
+------------+

-- Convert a value of the INT type into a JSON number.
select cast(123 as json);
-- The following result is returned:
+-----+
| _c0 |
+-----+
| 123 |
+-----+

-- Convert a value of the FLOAT type into a JSON number.
select cast(1.23 as json);
-- The following result is returned:
+-----+
| _c0 |
+-----+
| 1.23 |
+-----+

Example 10: Convert values between the BOOLEAN type and JSON types.

-- Convert a value of the BOOLEAN type into the BIGINT type.
select cast(true as json);
-- The following result is returned:
+-----+
| _c0 |
+-----+
| true |
+-----+

-- Convert a JSON boolean into a value of the BOOLEAN type.
select cast(json 'false' as boolean);
-- The following result is returned:
+------+
| _c0  |
+------+
| false |
+------+

-- Convert a JSON string into a value of the BOOLEAN type.
select cast(json '"abc"' as boolean);
-- The following result is returned:
+------+
| _c0  |
+------+
| true |
+------+

-- Convert a JSON array or JSON object into the BOOLEAN type.
select cast(json '[1,2]' as boolean);
-- An error is returned.
Unsupported cast from json array/object to boolean

Example 11: Convert between null values and values of JSON types.

-- Convert a null value into a value of the STRING type.
select json_type(cast(null as json));
-- The following result is returned:
+-----+
| _c0 |
+-----+
| NULL |
+-----+

Related functions

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

CAST is also classified as a function used in other business scenarios. For more information about functions that are used in other business scenarios, see Other functions.