array_join

SinceVersion 1.2.0

array_join

description

Syntax

VARCHAR array_join(ARRAY<T> arr, VARCHAR sep[, VARCHAR null_replace])

根据分隔符(sep)和替换NULL的字符串(null_replace), 将数组中的所有元素组合成一个新的字符串。 若sep为NULL，则返回值为NULL。 若null_replace为NULL，则返回值也为NULL。 若sep为空字符串，则不应用任何分隔符。 若null_replace为空字符串或者不指定，则直接丢弃数组中的NULL元素。

notice

仅支持向量化引擎中使用

example

2. mysql> set enable_vectorized_engine=true;

4. mysql> select k1, k2, array_join(k2, '_', 'null') from array_test order by k1;
5. +------+-----------------------------+------------------------------------+
6. | k1   | k2                          | array_join(`k2`, '_', 'null')      |
7. +------+-----------------------------+------------------------------------+
8. |  1   | [1, 2, 3, 4, 5]             | 1_2_3_4_5                          |
9. |  2   | [6, 7, 8]                   | 6_7_8                              |
10. |  3   | []                          |                                    |
11. |  4   | NULL                        | NULL                               |
12. |  5   | [1, 2, 3, 4, 5, 4, 3, 2, 1] | 1_2_3_4_5_4_3_2_1                  |
13. |  6   | [1, 2, 3, NULL]             | 1_2_3_null                         |
14. |  7   | [4, 5, 6, NULL, NULL]       | 4_5_6_null_null                    |
15. +------+-----------------------------+------------------------------------+

17. mysql> select k1, k2, array_join(k2, '_', 'null') from array_test01 order by k1;
18. +------+-----------------------------------+------------------------------------+
19. | k1   | k2                                | array_join(`k2`, '_', 'null')      |
20. +------+-----------------------------------+------------------------------------+
21. |  1   | ['a', 'b', 'c', 'd']              | a_b_c_d                            |
22. |  2   | ['e', 'f', 'g', 'h']              | e_f_g_h                            |
23. |  3   | [NULL, 'a', NULL, 'b', NULL, 'c'] | null_a_null_b_null_c               |
24. |  4   | ['d', 'e', NULL, ' ']             | d_e_null_                          |
25. |  5   | [' ', NULL, 'f', 'g']             |  _null_f_g                         |
26. +------+-----------------------------------+------------------------------------+

28. mysql> select k1, k2, array_join(k2, '_') from array_test order by k1;
29. +------+-----------------------------+----------------------------+
30. | k1   | k2                          | array_join(`k2`, '_')      |
31. +------+-----------------------------+----------------------------+
32. |  1   | [1, 2, 3, 4, 5]             | 1_2_3_4_5                  |
33. |  2   | [6, 7, 8]                   | 6_7_8                      |
34. |  3   | []                          |                            |
35. |  4   | NULL                        | NULL                       |
36. |  5   | [1, 2, 3, 4, 5, 4, 3, 2, 1] | 1_2_3_4_5_4_3_2_1          |
37. |  6   | [1, 2, 3, NULL]             | 1_2_3                      |
38. |  7   | [4, 5, 6, NULL, NULL]       | 4_5_6                      |
39. +------+-----------------------------+----------------------------+

41. mysql> select k1, k2, array_join(k2, '_') from array_test01 order by k1;
42. +------+-----------------------------------+----------------------------+
43. | k1   | k2                                | array_join(`k2`, '_')      |
44. +------+-----------------------------------+----------------------------+
45. |  1   | ['a', 'b', 'c', 'd']              | a_b_c_d                    |
46. |  2   | ['e', 'f', 'g', 'h']              | e_f_g_h                    |
47. |  3   | [NULL, 'a', NULL, 'b', NULL, 'c'] | a_b_c                      |
48. |  4   | ['d', 'e', NULL, ' ']             | d_e_                       |
49. |  5   | [' ', NULL, 'f', 'g']             |  _f_g                      |
50. +------+-----------------------------------+----------------------------+

keywords

ARRAY, JOIN, ARRAY_JOIN