TRIM_ARRAY scalar function

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

Read syntax diagramSkip visual syntax diagramTRIM_ARRAY( array-expression, numeric-expression)

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 array type. An associative array type cannot be specified.
numeric-expression
Specifies the number of elements that are trimmed from the end of the array. numeric-expression can be any numeric data type with a value that can be cast to INTEGER. The value of numeric-expression must be greater than or equal to 0 and less than or equal to the cardinality of array-expression.

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

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

The TRIM_ARRAY function can be invoked only in the following contexts:

  • A source value for SET assignment-statement or SQL PL assignment-statement, or a VALUES INTO statement
  • The value that is returned in a RETURN statement in an SQL scalar function

Notes

Syntax alternatives:

CAST (SQL-variable AS array-type) can be specified as an alternative to SQL-variable. CAST (SQL-parameter AS array-type) can be specified as an alternative to SQL-parameter.

ARRAY_TRIM is a synonym for the TRIM_ARRAY built in function.

Examples

Example 1:

Suppose that PHONENUMBERS is a user-defined array type that is defined as an ordinary array. RECENT_CALLS is an array variable of the PHONENUMBERS type. The following statement removes the last element from the array variable RECENT_CALLS.

SET RECENT_CALLS = TRIM_ARRAY(RECENT_CALLS,1);
Example 2:

Suppose that INTARRAY is a user-defined array type that is defined as an ordinary array with integer elements. SPECIALNUMBERS and LOWPRIMES are array variables of the INTARRAY type. The SPECIALNUMBERS array contains the values of all the prime numbers less than 1000, which is 168 values. The following statement assigns the 10 smallest prime numbers in the SPECIALNUMBERS array to the first 10 elements of the LOWPRIMES array.

SET LOWPRIMES = TRIM_ARRAY(SPECIALNUMBERS,CARDINALITY(SPECIALNUMBERS)-10);