---
title: "What are Array functions Read today"
description: "Array functions in Db2 explained with how to use."
url: https://srinimf.com/2015/05/18/db2-new-built-in-functions-in-version-11/
date: 2015-05-18
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2015/05/oracle-and-db2-array-functions.jpg?fit=1200%2C900&ssl=1
categories: ["DB2"]
tags: ["Array function in DB2 and Oracle", "DB2 V11 Built in functions"]
type: post
lang: en
---

# What are Array functions Read today

Arrays also called collections. The array functions help you use the values present in arrays are collections. I have listed Array functions from both DB2 and Oracle.

Related[DB2 V11 New Functions: Read Right Away](https://srinimf.com/2019/09/29/db2v11-top-built-in-functions-newly-added/)

## DB2 Array Functions

**ARRAY_AGG**

The ARRAY_AGG function returns an array in which each value of the input set is assigned to an element of the array. ARRAY_AGG can be invoked in the following situations:

Select list of a SELECT INTO statement

Select list of a fullselect in the definition of a cursor that is not scrollable

Select list of a scalar fullselect as a source data item for a SET assignment-statement (or SQL PL assignment-statement)

A RETURN statement in an SQL scalar function

**ARRAY_DELETE**

The ARRAY_DELETE function deletes elements from an array. This function can be specified only in the following specific contexts:

As a source value for a SET assignment-statement (or SQL PL assignment-statement) or VALUES INTO statement.

As the value to be returned in a RETURN statement in an SQL scalar function.

**ARRAY_FIRST**

The ARRAY_FIRST function returns the minimum array index value of an array. The data type of the result is the data type of the array index, which is INTEGER for an ordinary array. If array expression is not null and the array is not empty, the value of the result is the minimum array index value, which is 1 for an ordinary array

**ARRAY_LAS**T

The ARRAY_LAST function returns the maximum array index value of an array. The data type of the result is the data type of the array index, which is INTEGER for an ordinary array. If the array expression is not null and the array is not empty, the value of the result is the maximum array index value, which is the cardinality of the array for an ordinary array.

**ARRAY_NEXT**

The ARRAY_NEXT function returns the next larger array index value, relative to a specified array index value

**ARRAY_PRIOR**
The ARRAY_PRIOR function returns the next smaller array index value, relative to a specified array index value.

**CARDINALITY**
The CARDINALITY function returns the number of elements in an array. The data type of the result is BIGINT.

The result of the CARDINALITY function is as follows:

For an ordinary array, the result is the highest array index for which the array has an assigned element. Elements that have been assigned the null value are considered to be assigned elements.

For an associative array, the result is the actual number of unique array index values that are defined in array-expression.

For an empty array, the result is 0.

**MAX_CORDINALIT**Y

The MAX_CARDINALITY function returns the maximum number of elements that an array can contain. This value is the cardinality that was specified in the CREATE TYPE statement for an ordinary array type.

The result of the MAX_CARDINALITY function is as follows:

For an ordinary array, the result is the maximum number of elements that an array can contain.

For an associative array, the result is the null value

**TRIM_ARRAY**

The TRIM_ARRAY function deletes elements from the end of an ordinary array. It can be invoked only in the following contexts:

A source value for SET assignment-statement or SQL PL assignment-statement, or a VALUES INTOstatement

The value that is returned in a RETURN statement in an SQL scalar function

- ## Array Functions You can use only in PL/SQL

## Oracle Array Functions

| COUNT | Returns number of elements in the array |
| --- | --- |
| EXISTS | Returns Boolean **true** if the element at the specified index exists; otherwise returns **false** |
| EXTEND | Increases size of array by 1 or by the number specified, ie. EXTEND(n) **Cannot be used with associative arrays** |
| FIRST | Navigates to the first element in the array |
| NEXT | Navigates to the next element in the array |
| LAST | Navigates to the last element in the array |
| PRIOR | Navigates to the previous element in the array |
| TRIM | Removes the last element of the array, or the last n elements if a number is specified, ie. TRIM(n)  **Cannot be used with associative arrays** |
| DELETE | Removes all elements of an array, or the nth element, if a parameter is specified |

Also Read: [How to call Array Functions in Oracle](https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/collections.htm#BABGFBCF)

Related Posts

- [Differences: DB2 SELECT Vs. Sub-SELECT Vs. Full SELECT](https://srinimf.com/2015/10/19/differences-of-db2-select-sub-select-and-full-select/)DateOctober 19, 2015
- [DB2 V11 New Functions: Read Right Away](https://srinimf.com/2019/09/29/db2v11-top-built-in-functions-newly-added/)DateSeptember 29, 2019
- [How to Convert Array to Table: DB2 UNNEST Function](https://srinimf.com/2023/06/11/the-real-use-of-unnest-function-in-db2-sql/)DateJune 11, 2023

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2015%2F05%2F18%2Fdb2-new-built-in-functions-in-version-11%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2015%2F05%2Foracle-and-db2-array-functions.jpg%3Ffit%3D1200%252C900%26ssl%3D1&description=What%20are%20Array%20functions%20Read%20today)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2015/05/18/db2-new-built-in-functions-in-version-11/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2015/05/18/db2-new-built-in-functions-in-version-11/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2015/05/18/db2-new-built-in-functions-in-version-11/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2015/05/18/db2-new-built-in-functions-in-version-11/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20What%20are%20Array%20functions%20Read%20today&body=https%3A%2F%2Fsrinimf.com%2F2015%2F05%2F18%2Fdb2-new-built-in-functions-in-version-11%2F&share=email)
-

### Like this:

Like Loading…