MaxCompute:CAST

Basic types

JSON types

CAST converts values between JSON and these basic types: STRING, BIGINT, INT, TINYINT, SMALLINT, DOUBLE, FLOAT, BOOLEAN, and SQL type.

Convert basic types

-- STRING to BIGINT: returns 1
select cast('1' as bigint);

-- STRING to BOOLEAN: empty string returns false; any non-empty string returns true
select cast("" as boolean);
-- Result:
+-------+
| _c0   |
+-------+
| false |
+-------+

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

Convert strings to dates

By default, only strings in yyyy-MM-dd format convert successfully to DATE. Strings that include hours, minutes, and seconds return NULL.

select cast("2022-12-20" as date);
-- Result:
+------------+
| _c0        |
+------------+
| 2022-12-20 |
+------------+

select cast("2022-12-20 00:01:01" as date);
-- Result: NULL (full datetime format not enabled)
+------+
| _c0  |
+------+
| NULL |
+------+

To convert a datetime string (with hours, minutes, and seconds) to DATE, enable the full-format flag first:

set odps.sql.executionengine.enable.string.to.date.full.format=true;

select cast("2022-12-20 00:01:01" as date);
-- Result:
+------------+
| _c0        |
+------------+
| 2022-12-20 |
+------------+

Handle conversion failures

When a conversion is unsupported or the value cannot be parsed, CAST returns an error. The following is an incorrect usage example:

-- Incorrect usage: 'abc' cannot be cast to BIGINT
select cast('abc' as bigint);

Use odps.function.strictmode to control partial-match behavior:

-- strictmode=false: returns the numeric prefix
setproject odps.function.strictmode=false;
select cast('123abc' as bigint);
-- Result:
+-----+
| _c0 |
+-----+
| 123 |
+-----+

-- strictmode=true: returns an error
setproject odps.function.strictmode=true;
select cast('123abc' as bigint);
-- Error:
-- ODPS-0130071: Semantic analysis exception - physical plan generation failed:
-- java.lang.NumberFormatException:
-- ODPS-0123091: Illegal type cast - value '123abc' cannot be casted from String to Bigint

Control trailing zeros in DECIMAL output

The following examples use a table with DECIMAL columns.

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

-- trimzero=true: trailing zeros are removed
set odps.sql.decimal.tostring.trimzero=true;
select cast(round(dcm1,3) as string), cast(round(dcm2,3) as string) from mf_dot;
-- Result:
+--------+-------+
| _c0    | _c1   |
+--------+-------+
| 12.455 | 12.34 |
+--------+-------+

-- trimzero=false: trailing zeros are retained
set odps.sql.decimal.tostring.trimzero=false;
select cast(round(dcm1,3) as string), cast(round(dcm2,3) as string) from mf_dot;
-- Result:
+--------+--------+
| _c0    | _c1    |
+--------+--------+
| 12.455 | 12.340 |
+--------+--------+

-- trimzero has no effect on static values
set odps.sql.decimal.tostring.trimzero=false;
select cast(round(12345.120BD,3) as string);
-- Result:
+----------+
| _c0      |
+----------+
| 12345.12 |
+----------+

Convert between STRING and JSON

-- JSON number to STRING
select cast(json '123' as string);
-- Result: 123

-- JSON string to STRING
select cast(json '"abc"' as string);
-- Result: abc

-- JSON boolean to STRING
select cast(json 'true' as string);
-- Result: TRUE

-- JSON null to STRING
select cast(json 'null' as string);
-- Result: NULL

-- STRING to JSON (produces a JSON string type, not a JSON object)
select cast('{"a":2}' as json);
-- Result: "{\"a\":2}"

-- Incorrect usage: ARRAY and OBJECT types cannot be cast to STRING
select cast(json '{"a":2}' as string);
-- Error:
-- ODPS-0123091: Illegal type cast - Unsupported cast from json array/object to string

Convert between numeric types and JSON

-- JSON number to BIGINT
select cast(json '123' as bigint);
-- Result: 123

-- JSON string to FLOAT
select cast(json '"1.23"' as float);
-- Result: 1.23

-- JSON number to DOUBLE
select cast(json '1.23' as double);
-- Result: 1.23

-- INT to JSON
select cast(123 as json);
-- Result: 123

-- FLOAT to JSON
select cast(1.23 as json);
-- Result: 1.23

Convert between BOOLEAN and JSON

-- BOOLEAN to JSON
select cast(true as json);
-- Result: true

-- JSON boolean to BOOLEAN
select cast(json 'false' as boolean);
-- Result: false

-- JSON string to BOOLEAN: any non-empty JSON string returns true
select cast(json '"abc"' as boolean);
-- Result: true

-- Incorrect usage: ARRAY and OBJECT types cannot be cast to BOOLEAN
select cast(json '[1,2]' as boolean);
-- Error: Unsupported cast from json array/object to boolean

Convert between null and JSON

-- SQL null to JSON null
select json_type(cast(null as json));
-- Result: NULL