Array functions

ARR_AVGARR_AVG

Syntax:ARR_AVG( array )

Returns the average of elements in the array.

ARR_INTERSECTARR_INTERSECT

Syntax:ARR_INTERSECT( array_1, array_2, array_3 [ , ... ] )

Returns an array of elements that occur in every array provided as an argument. The function does not put such elements in any particular order. Duplicate elements are only added once.

ARR_MAXARR_MAX

Syntax:ARR_MAX( array )

Returns the greatest of elements in the array.

ARR_MINARR_MIN

Syntax:ARR_MIN( array )

Returns the least of elements in the array.

ARR_PRODUCTARR_PRODUCT

Syntax:ARR_PRODUCT( array )

Returns the product of elements in the array.

ARR_REMOVEARR_REMOVE

Syntax:ARR_REMOVE( array, value )

Removes all array elements equal to value.

ARR_STRARR_STR

Syntax:ARR_STR( array [ , delimiter [ , null_str ] ] )

Concatenates elements of the array array using delimiter as a delimiter (comma by default) and null_str as a NULL string (NULL items are skipped by default).

See also STR

ARR_SUMARR_SUM

Syntax:ARR_SUM( array )

Returns the sum of elements in the array.

ARRAYARRAY

Syntax:ARRAY( value_1, value_2, value_3 [ , ... ] )

Returns an array containing the passed values.

CAST_ARR_FLOATCAST_ARR_FLOAT

Syntax:CAST_ARR_FLOAT( array )

Converts array to an array of fractional numbers. The conversion rules are the same as for FLOAT.

CAST_ARR_INTCAST_ARR_INT

Syntax:CAST_ARR_INT( array )

Converts array to an array of integers. The conversion rules are the same as for INT.

CAST_ARR_STRCAST_ARR_STR

Syntax:CAST_ARR_STR( array )

Converts array to an array of strings. The conversion rules are the same as for STR.

CONTAINSCONTAINS

Syntax:CONTAINS( array, value )

Returns TRUE if array contains value.

CONTAINS_ALLCONTAINS_ALL

Syntax:CONTAINS_ALL( array_1, array_2 )

Returns TRUE if array_1 contains all elements of array_2.

CONTAINS_ANYCONTAINS_ANY

Syntax:CONTAINS_ANY( array_1, array_2 )

Returns TRUE if array_1 contains any elements of array_2.

CONTAINS_SUBSEQUENCECONTAINS_SUBSEQUENCE

Syntax:CONTAINS_SUBSEQUENCE( array_1, array_2 )

Returns TRUE if array_2 is a continuous subsequence of array_1. In other words, returns TRUE if and only if array1 = prefix + array2 + suffix.

COUNT_ITEMCOUNT_ITEM

Syntax:COUNT_ITEM( array, value )

Returns the number of elements in the array array equal to value. The type of value must match the type of the array elements.

GET_ITEMGET_ITEM

Syntax:GET_ITEM( array, index )

Returns the element with the index index from the array array. Index must be any integer. Indexes in an array begin with one. Returns the last item from the array if index is -1.

LENLEN

Syntax:LEN( value )

Returns the number of the value items.

REPLACEREPLACE

Syntax:REPLACE( array, old, new )

Replaces each array element equal to old with new.

SLICESLICE

Syntax:SLICE( array, offset, length )

Returns the part of array array of length length starting from index offset. Indexes in an array begin with one.

STARTSWITHSTARTSWITH

Syntax:STARTSWITH( array_1, array_2 )

Returns TRUE if array_1 starts with array_2.

UNNESTUNNEST

Syntax:UNNEST( array )

Expands the array array expression to a set of rows.