Array Functions

You are looking at an older version of the documentation. The latest version is found here.

Declaring 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.

Arrays are declared as follows:

BEGIN
	DECLARE STRING[] my_array = ARRAY('John','Paul', 'George','Stuart');
	SELECT my_array;
END;;
--Output: [John, Paul, George, Stuart]

Values in an array can be reassigned

BEGIN
	DECLARE STRING[] my_array = ARRAY('John','Paul', 'George','Stuart');
	my_array[4] = 'Ringo';
	SELECT my_array;
END;;
--Output: [John, Paul, George, Ringo]

Reassigning array values available since 2.4.25

ARRAY_GET

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

Syntax

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

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

ARRAY_LENGTH

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

Syntax

ARRAY_LENGTH(<array>)

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

Usage

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

ARRAY_ADD

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

Syntax

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

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

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

SELECT ARRAY_ADD(favourite_animals, ('sloth', 'mule'));
--Output: [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

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

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

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

ARRAY_IN

This function determines whether a given value matches any element in the provided array. If it does, the element index is returned; otherwise zero. Please note that it is case-sensitive.

Syntax

ARRAY_IN(<array>, <value>)

• <array> is the array you want to check for the 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 the same type of the elements in the array are (e.g. 1 won't match '1'), and it is case-sensitive;
• The function finds the index of a given element in a given array from the starting index 1. If the element matches more than one value, the index of the first occurrence is returned. If no values match, the result is zero. The data type of the result is integer.

Usage

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

SELECT ARRAY_IN(favourite_animals, 'goat');
--Output: 2
SELECT ARRAY_IN(favourite_animals, 'Goat');
--Output: 0
SELECT ARRAY_IN(favourite_animals, 'penguin, goat');
--Output: 0
SELECT ARRAY_IN(favourite_animals, ('penguin, goat'));
--Output: 0
SELECT ARRAY_IN(favourite_animals, 1);
--Output: 0
END

ARRAY_IN returns the position of an element since v2.4.21

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

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

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

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

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

SELECT array_like(favourite_numbers, '1');
--Output: 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

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

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

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