The following table lists the available array functions, and a description of their purpose.
| Function | Description |
|---|---|
| Array Constructor | Constructs a new instance of an ARRAY type and initializes it using the ARRAY constructor method or function. |
| Array Element Reference | Accesses the value of a specified element in an ARRAY data type value. |
| Array Scope Reference | References one or more elements of an ARRAY data type when invoking an ARRAY function. |
| Vector Constructor | Constructs a new instance of a VECTOR type and initializes it using the VECTOR constructor method or function. |
| ARRAY_AGG | An aggregate function for use with ARRAY data types that allows arrays to be created as part of the SELECT list of a query. See Expression With Order By (Teradata Database) for details about sorting the arguments of the ARRAY_AGG function. |
| ARRAY_COMPARE | Performs a pairwise comparison of the elements of two arrays and returns a value of 1, 0, or NULL to indicate whether or not the two array arguments contain the same element values. |
| ARRAY_CONCAT | Concatenates one-dimensional ARRAY data types and can be applied to a subset of the elements of the array. |
| ARRAY_GET | Returns the element value in array_expr that corresponds to the specified index position. |
| ARRAY_UPDATE | Updates all or a subset of the elements in array_expr to the specified new value. |
| ARRAY_UPDATE_STRIDE | Updates all or a subset of the elements in array_expr to the specified new value. The stride argument indicates how many elements should be skipped between each updated element. |
| CARDINALITY | Returns an integer representing the number of elements in an ARRAY data type that currently have assigned values, or the number of elements that are initialized within a specific scope reference. |
| Function | Description |
|---|---|
| ARRAY_AVG | Returns the average of all the element values in array_expr or the average of the elements within the specified scope. |
| ARRAY_COUNT_DISTINCT | Returns the number of distinct elements in array_expr, optionally matching a specific input value. |
| ARRAY_MIN | Returns the minimum of all element values in array_expr or the minimum of the elements within the specified scope. |
| ARRAY_MAX | Returns the maximum of all element values in array_expr or the maximum of the elements within the specified scope. |
| ARRAY_SUM | Returns a value representing the total sum of adding the values of each element in array_expr or the sum of the elements within the specified scope. |
| Function | Description |
|---|---|
| ARRAY_ADD | Performs an add arithmetic operation on an ARRAY value, whether referred to in its entirety or over a scope reference. |
| ARRAY_SUB | Performs a subtract arithmetic operation on an ARRAY value, whether referred to in its entirety or over a scope reference. |
| ARRAY_MUL | Performs a multiply arithmetic operation on an ARRAY value, whether referred to in its entirety or over a scope reference. |
| ARRAY_DIV | Performs a divide arithmetic operation on an ARRAY value, whether referred to in its entirety or over a scope reference. |
| ARRAY_MOD | Performs a modular arithmetic operation on an ARRAY value, whether referred to in its entirety or over a scope reference. |
| Function | Description |
|---|---|
| ARRAY_GT | Performs a greater than comparison on the individual elements of an ARRAY value, whether referred to in its entirety or over a scope reference. |
| ARRAY_GE | Performs a greater than or equal to comparison on the individual elements of an ARRAY value, whether referred to in its entirety or over a scope reference. |
| ARRAY_LT | Performs a less than comparison on the individual elements of an ARRAY value, whether referred to in its entirety or over a scope reference. |
| ARRAY_LE | Performs a less than or equal to comparison on the individual elements of an ARRAY value, whether referred to in its entirety or over a scope reference. |
| ARRAY_EQ | Performs an equal to comparison on the individual elements of an ARRAY value, whether referred to in its entirety or over a scope reference. |
| ARRAY_NE | Performs a not equal to comparison on the individual elements of an ARRAY value, whether referred to in its entirety or over a scope reference. |
| Function | Description |
|---|---|
| OCOUNT | Returns the number of elements in array_expr that contain either a NULL or non-NULL value. |
| ODELETE | Removes all elements from array_expr. |
| OEXISTS | Returns an integer value indicating whether the element specified by the index in array_expr contains a value or is in an uninitialized state. |
| OEXTEND | Allocates space for one or more new elements in array_expr. |
| OFIRST | Returns the lowest subscript value in array_expr as either an unsigned INTEGER value or a new instance of the predefined ARRAY type ArrayVec. |
| OLAST | Returns the highest subscript value with a populated element in array_expr. |
| OLIMIT | Returns the highest possible subscript value in array_expr as either an unsigned INTEGER value or a new instance of the predefined ARRAY type ArrayVec. |
| ONEXT | Returns the subscript of the element subsequent to the element specified for array_expr, if that element is currently populated. |
| OPRIOR | Returns the subscript of the element prior to the element specified for array_expr, if that element is currently populated. |
| OTRIM | Removes one or more populated elements from the end of array_expr. |
See the Teradata Database SQL Functions, Operators, Expressions, and Predicates document, B035-1145, for a detailed overview of these array functions.