Returns the position of the first occurrence of a given element in Array a. The position numbers of elements are counted from left to right and start from 1.
Syntax
bigint array_position(array<T> <a>, T <element>)Parameters
a: required. This parameter specifies an array.
Tinarray<T>specifies the data type of the elements in the array. The following data types are supported:TINYINT, SMALLINT, INT, and BIGINT
FLOAT and DOUBLE
BOOLEAN
DECIMAL and DECIMALVAL
DATE, DATETIME, TIMESTAMP, IntervalDayTime, and IntervalYearMonth
STRING, BINARY, VARCHAR, and CHAR
ARRAY, STRUCT, and MAP
element: required. The element whose position you want to query. The data type of this element must be the same as the data type of the elements in Array a.
Return value
A value of the BIGINT type is returned. The return value varies based on the following rules:
If the value of a or element is null, null is returned.
If the specified element is not found, 0 is returned.
Examples
Example 1: Return the position of the first occurrence of
1inarray(3, 2, 1). Sample statement:-- The return value is 3. select array_position(array(3, 2, 1), 1);Example 2: The specified element is null. Sample statement:
-- The return value is null. select array_position(array(3, 1, null), null);
Related functions
ARRAY_POSITION is a complex type function. For more information about the functions that are used to process data of complex data types, such as ARRAY, MAP, STRUCT, and JSON, see Complex type functions.