array_join function (Databricks SQL)

Concatenates the elements of array.

Syntax 

array_join(array, delimiter [, nullReplacement])

Arguments

  • array: Any ARRAY type, but its elements are interpreted as strings.

  • delimiter: A STRING used to separate the concatenated array elements.

  • nullReplacement: A STRING used to express a NULL value in the result.

Returns

A STRING where the elements of array are separated by delimiter and null elements are substituted for nullReplacement. If nullReplacement is omitted, null elements are filtered out. If any argument is NULL, the result is NULL.

Examples 

> SELECT array_join(array('hello', 'world'), ' ');
 hello world
> SELECT array_join(array('hello', NULL ,'world'), ' ');
 hello world
> SELECT array_join(array('hello', NULL ,'world'), ' ', ',');
 Hello,world