EXPLODE_SPLIT
Description
The explode_split
table function is used to split a string into multiple substrings according to the specified delimiter, and expand each substring into a separate row.
It should be used together with LATERAL VIEW
to flatten nested data structures into a standard flat table format.
The main difference between explode_split
and explode_split_outer
is how they handle null values.
Syntax
EXPLODE_SPLIT(<str>, <delimiter>)
Parameters
<str>
String type, the string to be split.<delimiter>
String type, the delimiter.
Return Value
- Returns a column composed of the split substrings, with column type String.
Usage Notes
- If
<str>
is NULL, 0 rows are returned. - If
<str>
is an empty string ("") or cannot be split, 1 row is returned. - If
<delimiter>
is NULL, 0 rows are returned. - If
<delimiter>
is an empty string (""),<str>
will be split by bytes(SPLIT_BY_STRING
).
Examples
- Prepare data
create table example(
k1 int
) properties(
"replication_num" = "1"
);
insert into example values(1); - Regular parameters
select * from example lateral view explode_split("ab,cd,ef", ",") t2 as c;
+------+------+
| k1 | c |
+------+------+
| 1 | ab |
| 1 | cd |
| 1 | ef |
+------+------+ - Empty string and unsplittable cases
select * from example lateral view explode_split("", ",") t2 as c;
+------+------+
| k1 | c |
+------+------+
| 1 | |
+------+------+select * from example lateral view explode_split("abc", ",") t2 as c;
+------+------+
| k1 | c |
+------+------+
| 1 | abc |
+------+------+ - NULL parameter
select * from example lateral view explode_split(NULL, ',') t2 as c;
Empty set (0.03 sec)
- Empty delimiter
select * from example lateral view explode_split('abc', '') t2 as c;
+------+------+
| k1 | c |
+------+------+
| 1 | a |
| 1 | b |
| 1 | c |
+------+------+ - Delimiter is NULL
select * from example lateral view explode_split('abc', null) t2 as c;
Empty set (0.03 sec)