CAST
Description
Converts an input into the specified type. For example, cast (input as BIGINT)
converts the input into a BIGINT value.
From v2.4, StarRocks supports conversion to the ARRAY type.
Syntax
cast (input as type)
Parameters
input
: the data you want to convert.
type
: the destination data type.
Return value
Returns a value whose data type is the same as type
.
Examples
Example 1: common data conversions
select cast('9.5' as DECIMAL(10,2));
+--------------------------------+
| CAST('9.5' AS DECIMAL64(10,2)) |
+--------------------------------+
| 9.50 |
+--------------------------------+
select cast(NULL AS INT);
+-------------------+
| CAST(NULL AS INT) |
+-------------------+
| NULL |
+-------------------+
select cast(true AS BOOLEAN);
+-----------------------+
| CAST(TRUE AS BOOLEAN) |
+-----------------------+
| 1 |
+-----------------------+
select cast (1 as BIGINT);
+-------------------+
| CAST(1 AS BIGINT) |
+-------------------+
| 1 |
+-------------------+
Example 2: Convert the input into ARRAY.
-- Convert string to ARRAY<ANY>.
select cast('[1,2,3]' as array<int>);
+-------------------------------+
| CAST('[1,2,3]' AS ARRAY<INT>) |
+-------------------------------+
| [1,2,3] |
+-------------------------------+
select cast('[1,2,3]' as array<bigint>);
+----------------------------------+
| CAST('[1,2,3]' AS ARRAY<BIGINT>) |
+----------------------------------+
| [1,2,3] |
+----------------------------------+
select cast('[1,2,3]' as array<string>);
+------------------------------------------+
| CAST('[1,2,3]' AS ARRAY<VARCHAR(65533)>) |
+------------------------------------------+
| ["1","2","3"] |
+------------------------------------------+
select cast('["a", "b", "c"]' as array<string>);
+--------------------------------------------------+
| CAST('["a", "b", "c"]' AS ARRAY<VARCHAR(65533)>) |
+--------------------------------------------------+
| ["a","b","c"] |
+--------------------------------------------------+
-- Convert JSON array to ARRAY<ANY>.
select cast(parse_json('[{"a":1}, {"a": 2}]') as array<json>);
+----------------------------------------------------------+
| CAST((parse_json('[{"a":1}, {"a": 2}]')) AS ARRAY<JSON>) |
+----------------------------------------------------------+
| ['{"a": 1}','{"a": 2}'] |
+----------------------------------------------------------+
select cast(parse_json('[1, 2, 3]') as array<int>);
+-----------------------------------------------+
| CAST((parse_json('[1, 2, 3]')) AS ARRAY<INT>) |
+-----------------------------------------------+
| [1,2,3] |
+-----------------------------------------------+
select cast(parse_json('["1","2","3"]') as array<string>);
+--------------------------------------------------------------+
| CAST((parse_json('["1","2","3"]')) AS ARRAY<VARCHAR(65533)>) |
+--------------------------------------------------------------+
| ["1","2","3"] |
+--------------------------------------------------------------+
Example 3: Convert data during loading.
curl --location-trusted -u <username>:<password> -T ~/user_data/bigint \
-H "columns: tmp_k1, k1=cast(tmp_k1 as BIGINT)" \
http://host:port/api/test/bigint/_stream_load
Note
If the original value is a floating-point value (such as 12.0), it will be converted to NULL. If you want to forcibly convert this type into BIGINT, see the following example:
curl --location-trusted -u <username>:<password> -T ~/user_data/bigint \
-H "columns: tmp_k1, k1=cast(cast(tmp_k1 as DOUBLE) as BIGINT)" \
http://host:port/api/test/bigint/_stream_load
MySQL > select cast(cast ("11.2" as double) as bigint);
+----------------------------------------+
| CAST(CAST('11.2' AS DOUBLE) AS BIGINT) |
+----------------------------------------+
| 11 |
+----------------------------------------+