array_join

SinceVersion 1.2.0

array_join

description

Syntax

  1. 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

  1. mysql> set enable_vectorized_engine=true;
  2. mysql> select k1, k2, array_join(k2, '_', 'null') from array_test order by k1;
  3. +------+-----------------------------+------------------------------------+
  4. | k1 | k2 | array_join(`k2`, '_', 'null') |
  5. +------+-----------------------------+------------------------------------+
  6. | 1 | [1, 2, 3, 4, 5] | 1_2_3_4_5 |
  7. | 2 | [6, 7, 8] | 6_7_8 |
  8. | 3 | [] | |
  9. | 4 | NULL | NULL |
  10. | 5 | [1, 2, 3, 4, 5, 4, 3, 2, 1] | 1_2_3_4_5_4_3_2_1 |
  11. | 6 | [1, 2, 3, NULL] | 1_2_3_null |
  12. | 7 | [4, 5, 6, NULL, NULL] | 4_5_6_null_null |
  13. +------+-----------------------------+------------------------------------+
  14. mysql> select k1, k2, array_join(k2, '_', 'null') from array_test01 order by k1;
  15. +------+-----------------------------------+------------------------------------+
  16. | k1 | k2 | array_join(`k2`, '_', 'null') |
  17. +------+-----------------------------------+------------------------------------+
  18. | 1 | ['a', 'b', 'c', 'd'] | a_b_c_d |
  19. | 2 | ['e', 'f', 'g', 'h'] | e_f_g_h |
  20. | 3 | [NULL, 'a', NULL, 'b', NULL, 'c'] | null_a_null_b_null_c |
  21. | 4 | ['d', 'e', NULL, ' '] | d_e_null_ |
  22. | 5 | [' ', NULL, 'f', 'g'] | _null_f_g |
  23. +------+-----------------------------------+------------------------------------+
  24. mysql> select k1, k2, array_join(k2, '_') from array_test order by k1;
  25. +------+-----------------------------+----------------------------+
  26. | k1 | k2 | array_join(`k2`, '_') |
  27. +------+-----------------------------+----------------------------+
  28. | 1 | [1, 2, 3, 4, 5] | 1_2_3_4_5 |
  29. | 2 | [6, 7, 8] | 6_7_8 |
  30. | 3 | [] | |
  31. | 4 | NULL | NULL |
  32. | 5 | [1, 2, 3, 4, 5, 4, 3, 2, 1] | 1_2_3_4_5_4_3_2_1 |
  33. | 6 | [1, 2, 3, NULL] | 1_2_3 |
  34. | 7 | [4, 5, 6, NULL, NULL] | 4_5_6 |
  35. +------+-----------------------------+----------------------------+
  36. mysql> select k1, k2, array_join(k2, '_') from array_test01 order by k1;
  37. +------+-----------------------------------+----------------------------+
  38. | k1 | k2 | array_join(`k2`, '_') |
  39. +------+-----------------------------------+----------------------------+
  40. | 1 | ['a', 'b', 'c', 'd'] | a_b_c_d |
  41. | 2 | ['e', 'f', 'g', 'h'] | e_f_g_h |
  42. | 3 | [NULL, 'a', NULL, 'b', NULL, 'c'] | a_b_c |
  43. | 4 | ['d', 'e', NULL, ' '] | d_e_ |
  44. | 5 | [' ', NULL, 'f', 'g'] | _f_g |
  45. +------+-----------------------------------+----------------------------+

keywords

ARRAY, JOIN, ARRAY_JOIN