ARRAY_DELETE scalar function

The ARRAY_DELETE function deletes elements from an array.

Read syntax diagramSkip visual syntax diagram ARRAY_DELETE ( array-expression ,array-index1,array-index2 )
The schema is SYSIBM.
array-expression
An SQL variable, SQL parameter, or global variable of an array type, or a CAST specification of a parameter marker to an array type.
array-index1
An expression that results in a value that can be assigned to the data type of the array index. If array-expression is an ordinary array, array-index1 must be the null value (SQLTATE 42815).
array-index2
An expression that results in a value that can be assigned to the data type of the array index. If array-expression is an ordinary array, array-index2 must be the null value. If array-index2 is specified and is a non-null value, then array-index1 must be a non-null value that is less than the value of array-index2 (SQLSTATE 42815).
The result of the function has the same data type as array-expression. The result array is a copy of array-expression, with the following modifications:
  • If the optional arguments are not specified or they are the null value, all of the elements of array-expression are deleted and the cardinality of the result array value is 0.
  • If only array-index1 is specified with a non-null value, the array element at index value array-index1 is deleted.
  • If array-index2 is also specified with a non-null value, then the elements ranging from index value array-index1 to array-index2 (inclusive) are deleted.

The result can be null; if array-expression is null, the result is the null value.

Examples

  1. Delete all the elements from the ordinary array variable RECENT_CALLS of array type PHONENUMBERS .
    SETRECENT_CALLS = ARRAY_DELETE(RECENT_CALLS)
  2. A supplier has discontinued some of their products. Delete the elements from the associative array variable FLOOR_TILES of array type PRODUCTS from index value 'PK5100' to index value 'PS2500'.
    SETFLOOR_TILES = ARRAY_DELETE(FLOOR_TILES,'PK5100','PS2500')