EXPLODE_SPLIT
更新时间：2025-10-16
描述
explode_split 表函数用于将字符串按照指定分隔符拆分为多个子字符串，并将每个子字符串展开为一行。每个子字符串作为单独的行返回，通常与 LATERAL VIEW 一起使用，便于将长字符串拆解为单独的部分，进行更细粒度的查询。
explode_split_outer 与 explode_split 类似。但与 explode_split 不同的是，它在处理空值和 NULL 值时会有不同的行为，能够处理空的或 NULL 的字符串。
语法
SQL
1EXPLODE_SPLIT(<str>, <delimiter>)
2EXPLODE_SPLIT_OUTER(<str>, <delimiter>)参数
| 参数 | 说明 |
|---|---|
<str> |
字符串类型 |
<delimiter> |
分割符 |
返回值
返回拆分后的子字符串序列。如果字符串为空或 NULL，不返回任何行。
举例
SQL
1select * from example1 order by k1;
Text
1+------+---------+
2| k1 | k2 |
3+------+---------+
4| 1 | |
5| 2 | NULL |
6| 3 | , |
7| 4 | 1 |
8| 5 | 1,2,3 |
9| 6 | a, b, c |
10+------+---------+
SQL
1select k1, e1 from example1 lateral view explode_split(k2, ',') tmp1 as e1 where k1 = 1 order by k1, e1;
Text
1+------+------+
2| k1 | e1 |
3+------+------+
4| 1 | |
5+------+------+
SQL
1select k1, e1 from example1 lateral view explode_split(k2, ',') tmp1 as e1 where k1 = 2 order by k1, e1;
2Empty set
SQL
1select k1, e1 from example1 lateral view explode_split(k2, ',') tmp1 as e1 where k1 = 3 order by k1, e1;
Text
1+------+------+
2| k1 | e1 |
3+------+------+
4| 3 | |
5+------+------+
SQL
1select k1, e1 from example1 lateral view explode_split(k2, ',') tmp1 as e1 where k1 = 4 order by k1, e1;
Text
1+------+------+
2| k1 | e1 |
3+------+------+
4| 4 | 1 |
5+------+------+
SQL
1select k1, e1 from example1 lateral view explode_split(k2, ',') tmp1 as e1 where k1 = 5 order by k1, e1;
Text
1+------+------+
2| k1 | e1 |
3+------+------+
4| 5 | 2 |
5| 5 | 3 |
6| 5 | 1 |
7+------+------+
SQL
1select k1, e1 from example1 lateral view explode_split(k2, ',') tmp1 as e1 where k1 = 6 order by k1, e1;
Text
1+------+------+
2| k1 | e1 |
3+------+------+
4| 6 | b |
5| 6 | c |
6| 6 | a |
7+------+------+
SQL
1CREATE TABLE example2 (
2 id INT,
3 str string null
4)DUPLICATE KEY(id)
5DISTRIBUTED BY HASH(`id`) BUCKETS AUTO
6PROPERTIES (
7"replication_allocation" = "tag.location.default: 1");
SQL
1insert into example2 values (1,''),(2,NUll),(3,"1"),(4,"1,2,3"),(5,"a,b,c");
SQL
1select id, e1 from example2 lateral view explode_split(str, ',') tmp1 as e1 where id = 2 order by id, e1;
2Empty set (0.02 sec)
SQL
1select id, e1 from example2 lateral view explode_split_outer(str, ',') tmp1 as e1 where id = 2 order by id, e1;
Text
1+------+------+
2| id | e1 |
3+------+------+
4| 2 | NULL |
5+------+------+