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]
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_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 aWHERE LIKE
statement; - The function returns
TRUE
if a match has been found; otherwise, it returnsFALSE
.
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 returnsFALSE
.
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