explode_split
Description
This is a table function that is used in combination with Lateral View.
It splits a string into multiple substrings based on the specified delimiter.
Syntax:
explode_split(str, delimiter)
Example
Original table data:
mysql> select * from example1 order by k1;
+------+---------+
| k1 | k2 |
+------+---------+
| 1 | |
| 2 | NULL |
| 3 | , |
| 4 | 1 |
| 5 | 1,2,3 |
| 6 | a, b, c |
+------+---------+
Lateral View:
mysql> select k1, e1 from example1 lateral view explode_split(k2, ',') tmp1 as e1 where k1 = 1 order by k1, e1;
+------+------+
| k1 | e1 |
+------+------+
| 1 | |
+------+------+
mysql> select k1, e1 from example1 lateral view explode_split(k2, ',') tmp1 as e1 where k1 = 2 order by k1, e1;
+------+------+
| k1 | e1 |
+------+------+
| 2 | NULL |
+------+------+
mysql> select k1, e1 from example1 lateral view explode_split(k2, ',') tmp1 as e1 where k1 = 3 order by k1, e1;
+------+------+
| k1 | e1 |
+------+------+
| 3 | |
| 3 | |
+------+------+
mysql> select k1, e1 from example1 lateral view explode_split(k2, ',') tmp1 as e1 where k1 = 4 order by k1, e1;
+------+------+
| k1 | e1 |
+------+------+
| 4 | 1 |
+------+------+
mysql> select k1, e1 from example1 lateral view explode_split(k2, ',') tmp1 as e1 where k1 = 5 order by k1, e1;
+------+------+
| k1 | e1 |
+------+------+
| 5 | 1 |
| 5 | 2 |
| 5 | 3 |
+------+------+
mysql> select k1, e1 from example1 lateral view explode_split(k2, ',') tmp1 as e1 where k1 = 6 order by k1, e1;
+------+------+
| k1 | e1 |
+------+------+
| 6 | b |
| 6 | c |
| 6 | a |
+------+------+
Keywords
explode,split,explode_split