array_position function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Returns the position of the first occurrence of element in array.

Syntax 

array_position(array, element)

Arguments

  • array: An ARRAY with comparable elements.

  • element: An expression matching the types of the elements in array.

Returns

A BIGINT.

Array indexing starts at 1. If the element value is NULL, a NULL is returned. If the element is not found in the array, a 0 is returned.

Examples 

-- 1 exists twice. The function returns the first position
> SELECT array_position(array(3, 2, 1, 4, 1), 1);
 3

-- this function cannot be used to find the position of a NULL element.
> SELECT array_position(array(3, NULL, 1), NULL)
 NULL

> SELECT array_position(array(3, 2, 1), NULL)
 NULL

-- The element is not found in the array
> SELECT array_position(array(3, 2, 1, 4, 1), 5)
 0