array_join

array_join

description

Syntax

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

Combines all elements in the array to generate a new string according to the separator (sep) and the string to replace NULL (null_replace). If sep is NULL, return NULL. If null_replace is NULL, return NULL. If sep is an empty string, no delimiter is applied. If null_replace is an empty string or not specified, the NULL elements in the array are discarded directly.

example

  1. mysql> select k1, k2, array_join(k2, '_', 'null') from array_test order by k1;
  2. +------+-----------------------------+------------------------------------+
  3. | k1   | k2                          | array_join(`k2`, '_', 'null')      |
  4. +------+-----------------------------+------------------------------------+
  5. |  1   | [1, 2, 3, 4, 5]             | 1_2_3_4_5                          |
  6. |  2   | [6, 7, 8]                   | 6_7_8                              |
  7. |  3   | []                          |                                    |
  8. |  4   | NULL                        | NULL                               |
  9. |  5   | [1, 2, 3, 4, 5, 4, 3, 2, 1] | 1_2_3_4_5_4_3_2_1                  |
  10. |  6   | [1, 2, 3, NULL]             | 1_2_3_null                         |
  11. |  7   | [4, 5, 6, NULL, NULL]       | 4_5_6_null_null                    |
  12. +------+-----------------------------+------------------------------------+
  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. +------+-----------------------------------+------------------------------------+
  25. mysql> select k1, k2, array_join(k2, '_') from array_test order by k1;
  26. +------+-----------------------------+----------------------------+
  27. | k1   | k2                          | array_join(`k2`, '_')      |
  28. +------+-----------------------------+----------------------------+
  29. |  1   | [1, 2, 3, 4, 5]             | 1_2_3_4_5                  |
  30. |  2   | [6, 7, 8]                   | 6_7_8                      |
  31. |  3   | []                          |                            |
  32. |  4   | NULL                        | NULL                       |
  33. |  5   | [1, 2, 3, 4, 5, 4, 3, 2, 1] | 1_2_3_4_5_4_3_2_1          |
  34. |  6   | [1, 2, 3, NULL]             | 1_2_3                      |
  35. |  7   | [4, 5, 6, NULL, NULL]       | 4_5_6                      |
  36. +------+-----------------------------+----------------------------+
  38. mysql> select k1, k2, array_join(k2, '_') from array_test01 order by k1;
  39. +------+-----------------------------------+----------------------------+
  40. | k1   | k2                                | array_join(`k2`, '_')      |
  41. +------+-----------------------------------+----------------------------+
  42. |  1   | ['a', 'b', 'c', 'd']              | a_b_c_d                    |
  43. |  2   | ['e', 'f', 'g', 'h']              | e_f_g_h                    |
  44. |  3   | [NULL, 'a', NULL, 'b', NULL, 'c'] | a_b_c                      |
  45. |  4   | ['d', 'e', NULL, ' ']             | d_e_                       |
  46. |  5   | [' ', NULL, 'f', 'g']             |  _f_g                      |
  47. +------+-----------------------------------+----------------------------+

keywords

ARRAY, JOIN, ARRAY_JOIN