array_insert
function
Applies to: Databricks SQL Databricks Runtime 13.3 LTS and above
Returns an expanded array
where elem
is inserted at the index
position.
Arguments
array
: An ARRAY.index
: A non-zero INTEGER expression specifying where to insertelem
. If index is negativeelem
is inserted relative to the end of the array.elem
: An expression of the same type as the elements ofarray
.
Notes
All elements starting with index
are shifted by one position to make space for elem
at index
.
If index
is outside the cardinality of array
the array is padded with NULL
s.
Examples
> SELECT array_insert(array('a', 'b', 'c'), 1, 'z');
["z","a","b","c"]
> SELECT array_insert(array('a', 'b', 'c'), 0, 'z');
Error
> SELECT array_insert(array('a', 'b', 'c'), -1, 'z');
["a","b","c","z"]
> SELECT array_insert(array('a', 'b', 'c'), 5, 'z');
["a","b","c",NULL,"z"]
> SELECT array_insert(array('a', 'b', 'c'), -5, 'z');
["z",NULL,"a","b","c"]
> SELECT array_insert(array('a', 'b', 'c'), 2, cast(NULL AS STRING));
["a",NULL,"b","c"]