Array Functions

As their name implies, array functions allow you to work with arrays. One important thing to keep in mind is that in the Data Virtuality Server, array indexes start at position 1. This means that my_array[1] will return the first element in the array, whereas my_array[0] will return NULL.

ARRAY_GET

This function returns the element of an array at the given index as object.

Syntax

SQL

ARRAY_GET(<array>, <index>)

<array> is the array you want to extract an element from, and it has to be of type object;
<index> is the position of the element to be extracted (starting from position 1), and it must be of type integer.

Usage

SQL

BEGIN
DECLARE OBJECT favourite_animals = ARRAY('penguin', 'goat', 'goose', 'sloth', 'mule');
SELECT ARRAY_GET(favourite_animals, 2);
END
--Outcome: Goat

ARRAY_LENGTH

This function returns the length for a given array as integer.

Syntax

SQL

ARRAY_LENGTH(<array>)

<array> is the array the length of which you want to determine, and it is of type object.

Usage

SQL

BEGIN
DECLARE OBJECT favourite_animals = ARRAY('penguin', 'goat', 'goose', 'sloth', 'mule');
SELECT ARRAY_LENGTH(favourite_animals);
END
--Outcome: 5

ARRAY_ADD

This function adds an array or a single value to a given array.

Syntax

SQL

newArray = ARRAY_ADD(<array>, <array or single_value>)

<array> is the array you want to add an element to, and it has to be of type object;
<array or single_value> is a single element or an array of elements that you want to add to an array, and both have to be of the same type that the elements in the given array are of;
An exception will be thrown if the given parameters mismatch the required types;
The function returns a new, merged array of type object.

Usage

SQL

BEGIN
DECLARE OBJECT favourite_animals = ARRAY('penguin', 'goat', 'goose');

SELECT ARRAY_ADD(favourite_animals, 'sloth');
--Outcome: [penguin, goat, goose, sloth]

SELECT ARRAY_ADD(favourite_animals, ('sloth', 'mule'));
--Outcome: [penguin, goat, goose, sloth, mule]
END

ARRAY_SUB

This function returns an array with the elements of the given array for the given amount starting at the given position.

Syntax

SQL

ARRAY_SUB(<array>, <start>, <count>)

<array> is the array you want to extract subelements from, and it has to be of type object;
<start> is the position in the array to start from (please remember that the first position is position 1!), and it has to be of type integer. If <start> exceeds the length of the array, NULL is returned;
<count> is the number of elements you want to extract from the array, starting from <start>. It has to be of type integer and needs to be a positive number. If <count> exceeds the remaining elements after <start>, the function will return all elements it finds till the end is reached;
An exception will be thrown if the given parameters mismatch the required types;
The function returns a new, merged array of the type object.

Usage

SQL

BEGIN
DECLARE OBJECT favourite_animals = ARRAY('penguin', 'goat', 'goose', 'sloth', 'mule');

SELECT ARRAY_SUB(favourite_animals, 3, 2);
--Outcome: [goose, sloth]
SELECT ARRAY_SUB(favourite_animals, 3, 10);
--Outcome: [goose, sloth, mule]
SELECT ARRAY_SUB(favourite_animals, 7, 10);
--Outcome: NULL
SELECT ARRAY_SUB(favourite_animals, 0, 5);
--Outcome: NULL
END

ARRAY_IN

This function determines whether the given value matches an element in the array or not. Please note that it is case-sensitive.

Syntax

SQL

ARRAY_IN(<array>, <value>)

<array> is the array you want to check for a subelement, and it has to be of type object;
<value> is the subelement you want to know of if it is present in the array; it can be of any type, but should be of the same type the elements in the array are of (e.g. 1 won't match '1'), and it is case-sensitive;
The function returns TRUE if a match has been found; otherwise, it returns FALSE.

Usage

SQL

BEGIN
DECLARE OBJECT favourite_animals = ARRAY('penguin', 'goat', 'goose', 'sloth', 'mule');

SELECT ARRAY_IN(favourite_animals, 'goat');
--Outcome: TRUE
SELECT ARRAY_IN(favourite_animals, 'Goat');
--Outcome: FALSE
SELECT ARRAY_IN(favourite_animals, 'penguin, goat');
--Outcome: FALSE
SELECT ARRAY_IN(favourite_animals, ('penguin, goat'));
--Outcome: FALSE
END

ARRAY_LIKE

This function performs a LIKE search on an array and determines whether a matching element was found or not. Please note that it is case-sensitive.

Syntax

SQL

ARRAY_LIKE(<array>, <like-string>)

<array> is the array you want to perform the search on, and it has to be of type object;
<like-string> is the string you want to look for in the array; it has to be of type string and is case-sensitive. Please note that you would use the <like-string> just like you would use it in a WHERE LIKE statement;
The function returns TRUE if a match has been found; otherwise, it returns FALSE.

Usage

SQL

BEGIN
DECLARE OBJECT favourite_animals = ARRAY('penguin', 'goat', 'goose', 'sloth', 'mule');

SELECT ARRAY_LIKE(favourite_animals, 'peng%');
--Outcome: TRUE
SELECT ARRAY_LIKE(favourite_animals, 'penguin');
--Outcome: TRUE
SELECT ARRAY_LIKE(favourite_animals, 'peng');
--Outcome: FALSE
SELECT ARRAY_LIKE(favourite_animals, 'Peng%');
--Outcome: FALSE

DECLARE object favourite_numbers = array(1, 3, 5);

SELECT array_like(favourite_numbers, '1');
--Outcome: TRUE
END

ARRAY_LIKE_REGEX

This function performs a RegEx search on an array and determines whether a matching element was found or not.

Syntax

SQL

ARRAY_LIKE_REGEX(<array>, <regex-string>)

<array> is the array you want to perform the search on, and it has to be of type object;
<regex-string> is the regular expression you want to check against the array's elements, and it has to be of type string;
This function returns TRUE if a match has been found; otherwise, it returns FALSE.

Usage

SQL

BEGIN
DECLARE OBJECT favourite_animals = ARRAY('penguin', 'goat', 'goose', 'sloth', 'mule');

SELECT ARRAY_LIKE_REGEX(favourite_animals, 'penguin');
--Outcome: TRUE
SELECT ARRAY_LIKE_REGEX(favourite_animals, 'pen[a-z]*n');
--Outcome: TRUE
SELECT ARRAY_LIKE_REGEX(favourite_animals, 'pen');
--Outcome: TRUE
SELECT ARRAY_LIKE_REGEX(favourite_animals, 'PENGUIN');
--Outcome: FALSE
SELECT ARRAY_LIKE_REGEX(favourite_animals, '(?i)PENGUIN');
--Outcome: TRUE
END