array_join
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.
mysql> select k1, k2, array_join(k2, '_', 'null') from array_test order by k1; +------+-----------------------------+------------------------------------+ | k1 | k2 | array_join(`k2`, '_', 'null') | +------+-----------------------------+------------------------------------+ | 1 | [1, 2, 3, 4, 5] | 1_2_3_4_5 | | 2 | [6, 7, 8] | 6_7_8 | | 3 | [] | | | 4 | NULL | NULL | | 5 | [1, 2, 3, 4, 5, 4, 3, 2, 1] | 1_2_3_4_5_4_3_2_1 | | 6 | [1, 2, 3, NULL] | 1_2_3_null | | 7 | [4, 5, 6, NULL, NULL] | 4_5_6_null_null | +------+-----------------------------+------------------------------------+ mysql> select k1, k2, array_join(k2, '_', 'null') from array_test01 order by k1; +------+-----------------------------------+------------------------------------+ | k1 | k2 | array_join(`k2`, '_', 'null') | +------+-----------------------------------+------------------------------------+ | 1 | ['a', 'b', 'c', 'd'] | a_b_c_d | | 2 | ['e', 'f', 'g', 'h'] | e_f_g_h | | 3 | [NULL, 'a', NULL, 'b', NULL, 'c'] | null_a_null_b_null_c | | 4 | ['d', 'e', NULL, ' '] | d_e_null_ | | 5 | [' ', NULL, 'f', 'g'] | _null_f_g | +------+-----------------------------------+------------------------------------+ mysql> select k1, k2, array_join(k2, '_') from array_test order by k1; +------+-----------------------------+----------------------------+ | k1 | k2 | array_join(`k2`, '_') | +------+-----------------------------+----------------------------+ | 1 | [1, 2, 3, 4, 5] | 1_2_3_4_5 | | 2 | [6, 7, 8] | 6_7_8 | | 3 | [] | | | 4 | NULL | NULL | | 5 | [1, 2, 3, 4, 5, 4, 3, 2, 1] | 1_2_3_4_5_4_3_2_1 | | 6 | [1, 2, 3, NULL] | 1_2_3 | | 7 | [4, 5, 6, NULL, NULL] | 4_5_6 | +------+-----------------------------+----------------------------+ mysql> select k1, k2, array_join(k2, '_') from array_test01 order by k1; +------+-----------------------------------+----------------------------+ | k1 | k2 | array_join(`k2`, '_') | +------+-----------------------------------+----------------------------+ | 1 | ['a', 'b', 'c', 'd'] | a_b_c_d | | 2 | ['e', 'f', 'g', 'h'] | e_f_g_h | | 3 | [NULL, 'a', NULL, 'b', NULL, 'c'] | a_b_c | | 4 | ['d', 'e', NULL, ' '] | d_e_ | | 5 | [' ', NULL, 'f', 'g'] | _f_g | +------+-----------------------------------+----------------------------+
ARRAY, JOIN, ARRAY_JOIN