array_position function

Applies to: Databricks SQL 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

Related functions

• [ ] (bracket sign) operator
• array_contains function
• arrays_overlap function