TRIM_ARRAY scalar function

The TRIM_ARRAY function deletes elements from the end of an array.

The schema is SYSIBM.

array-expression: An SQL variable, SQL parameter, or global variable of an ordinary array type, or a CAST specification of a parameter marker to an ordinary array type, or a column in a Hadoop table defined as an array. An associative array data type cannot be specified (SQLSTATE 42884).
numeric-expression: Specifies the number of elements trimmed from the end of the array. The numeric expression can be of any numeric data type with a value that can be cast to INTEGER. The value of the numeric expression must be between 0 and the cardinality of the array expression (SQLSTATE 2202E).

Result

The function returns a value with the same array type as the array expression but with the cardinality reduced by the value of INTEGER(numeric-expression).

The result can be null; if either argument is null, the result is the null value.

Rules

The TRIM_ARRAY function is not supported for associative arrays (SQLSTATE 42884).
The TRIM_ARRAY function can only be used on the right side of an assignment statement in contexts where arrays are supported (SQLSTATE 42884).

Examples

Example 1: Remove the last element from the array variable RECENT_CALLS.
```
   SET RECENT_CALLS = TRIM_ARRAY(RECENT_CALLS, 1)
```
Example 2: Assign only the first two elements from the array variable SPECIALNUMBERS to the SQL array variable EULER_CONST:
```
SET EULER_CONST = TRIM_ARRAY(SPECIALNUMBERS, 8)
```
The result is that EULER_CONST will be assigned an array with two elements, the first element value is 2.71828183 and the second element value is the null value.

Remove the last element from the array in a Hadoop table.

CREATE HADOOP TABLE t1 (c1 INT, c2 DOUBLE ARRAY[5]);
SELECT c1, CARDINALITY( TRIM_ARRAY(c2, 1) ) FROM t1;