इसके माध्यम से साझा किया गया

array_insert function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 13.3 LTS and above

Returns an expanded array where elem is inserted at the index position.

Syntax

array_insert(array, index, elem)

Arguments

  • array: An ARRAY.
  • index: A non-zero INTEGER expression specifying where to insert elem. If index is negative elem is inserted relative to the end of the array.
  • elem: An expression of the same type as the elements of array.

Returns

An ARRAY of the same type as array.

Azure Databricks raises INVALID_INDEX_OF_ZERO if index is 0.

Azure Databricks raises COLLECTION_SIZE_LIMIT_EXCEEDED if the result exceeds the array size limit.

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

Common error conditions

Examples

> SELECT array_insert(array('a', 'b', 'c'), 1, 'z');
 ["z","a","b","c"]

> SELECT array_insert(array('a', 'b', 'c'), 0, 'z');
 Error: INVALID_INDEX_OF_ZERO

> 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"]
  • Last updated on