explode_split

description

表函数，需配合 Lateral View 使用。

将一个字符串按指定的分隔符分割成多个子串。

syntax

explode_split(str, delimiter)

example

原表数据：

1. mysql> select * from example1 order by k1;
2. +------+---------+
3. | k1   | k2      |
4. +------+---------+
5. |    1 |         |
6. |    2 | NULL    |
7. |    3 | ,       |
8. |    4 | 1       |
9. |    5 | 1,2,3   |
10. |    6 | a, b, c |
11. +------+---------+

Lateral View:

1. mysql> select k1, e1 from example1 lateral view explode_split(k2, ',') tmp1 as e1 where k1 = 1 order by k1, e1;
2. +------+------+
3. | k1   | e1   |
4. +------+------+
5. |    1 |      |
6. +------+------+

8. mysql> select k1, e1 from example1 lateral view explode_split(k2, ',') tmp1 as e1 where k1 = 2 order by k1, e1;
9. Empty set

11. mysql> select k1, e1 from example1 lateral view explode_split(k2, ',') tmp1 as e1 where k1 = 3 order by k1, e1;
12. +------+------+
13. | k1   | e1   |
14. +------+------+
15. |    3 |      |
16. +------+------+

18. mysql> select k1, e1 from example1 lateral view explode_split(k2, ',') tmp1 as e1 where k1 = 4 order by k1, e1;
19. +------+------+
20. | k1   | e1   |
21. +------+------+
22. |    4 | 1    |
23. +------+------+

25. mysql> select k1, e1 from example1 lateral view explode_split(k2, ',') tmp1 as e1 where k1 = 5 order by k1, e1;
26. +------+------+
27. | k1   | e1   |
28. +------+------+
29. |    5 | 2    |
30. |    5 | 3    |
31. |    5 | 1    |
32. +------+------+

34. mysql> select k1, e1 from example1 lateral view explode_split(k2, ',') tmp1 as e1 where k1 = 6 order by k1, e1;
35. +------+------+
36. | k1   | e1   |
37. +------+------+
38. |    6 |  b   |
39. |    6 |  c   |
40. |    6 |  a   |
41. +------+------+

keywords

explode,split,explode_split