ARRAY_DELETE scalar function

The ARRAY_DELETE function deletes elements from an array.

Read syntax diagramSkip visual syntax diagramARRAY_DELETE( array-expression, array-index1, array-index2)

The schema is SYSIBM.

array-expression
Start of changeAn SQL variable, SQL parameter, or global variable of an array type, or a CAST specification that specifies an SQL variable, SQL parameter, global variable, or parameter marker as the source value.End of change
array-index1
An expression that results in a value that is castable to the data type of the array index. If array-expression is an ordinary array, array-index1 must be the null value.
array-index2
An expression that results in a value that is castable 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, array-index1 must be a non-null value that is less than the value of array-index2. If array-index2 is the null value, ARRAY_DELETE is evaluated as if array-index2 was not specified.

The result of ARRAY_DELETE has the same data type as array-expression.

If array-index1 and array-index2 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 specified with a non-null value, the elements ranging from index value array-index1 to array-index2, inclusive, are deleted.

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

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

  • A source value for SET assignment-statement, an 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.

Examples

Example 1: Suppose that ordinary array variable RECENT_CALLS has the array type PHONENUMBERS. Use ARRAY_DELETE to delete all the elements from RECENT_CALLS. Assign the result to the RECENT_CALLS array.

SET RECENT_CALLS = ARRAY_DELETE(RECENT_CALLS);

After the SET statement is executed, RECENT_CALLS is an empty array, which has a cardinality of zero.

An equivalent way of setting RECENT_CALLS to an empty array is to use an array constructor:

SET RECENT_CALLS = ARRAY[ ];

Example 2: Suppose that PRODUCTS is defined as an associative array type with VARCHAR values for the array index, and that variables FLOOR_TILES and REMAINIING_TILES are defined as arrays of the PRODUCTS array type. Use ARRAY_DELETE to assign the elements from the FLOOR_TILES array variable that do not have an index value between 'PK5100' and 'PS2500', inclusive, to the REMAINING_TILES array variable.

SET REMAINING_TILES = ARRAY_DELETE(FLOOR_TILES,'PK5100','PS2500');