Array Functions
This section describes array functions, including their syntax, parameters, and usage examples.
Function List
Function |
Description |
---|---|
Constructs the input parameters of the same type into an array. |
|
Obtains the subscript of a specified element, starting from 1. If the specified element was not found, 0 is returned. |
|
Calculates the number of elements in an array. |
|
Calculates the number of elements in an array. It is equivalent to the cardinality function. |
|
Determines whether an array contains a specified element. If the array contains the element, true is returned. |
|
Determines whether an array contains a specified element. If the array contains the element, true is returned. This function is equivalent to the contains function. |
|
Adds a specified element to the beginning of an array. |
|
Adds a specified element to the end of an array. |
|
Returns a portion of an array from index start to index end. |
|
Uses a specified delimiter (str) to join all elements of an array (arr) into a string. |
|
Uses a specified delimiter (str2) to split a string (str1) into an array. |
|
Returns the array element at the provided 0-based index. |
|
Returns the array element at the provided 1-based index. |
|
Returns the 0-based index of expr that appears for the first time in an array. If expr does not appear, it returns -1. |
|
Returns the 1-based index of expr that appears for the first time in an array. If expr does not appear, it returns -1. |
array
Constructs the parameters of the same type into an array.
Syntax: array[expr1,expr...]
Parameter |
Description |
Type |
Mandatory |
---|---|---|---|
expr |
Raw data. |
String/Integer/Long/Double/Float |
Yes |
Return value type: array
Example: SELECT ARRAY['1','2','3','4','5']
Type |
Query Statement |
Returned Result |
---|---|---|
Scenario |
ARRAY['1','2','3','4','5'] |
["1", "2", "3", "4", "5"] |
array_position
Obtains the subscript of a specified element, starting from 1. If the specified element was not found, 0 is returned.
Syntax: array_position(expr, ele)
Parameter |
Description |
Type |
Mandatory |
---|---|---|---|
expr |
Original array. |
Array |
Yes |
ele |
Specified element. |
An element in the array. It must be of the same type as the other elements in the array. |
Yes |
Return value type: integer
Example: SELECT ARRAY_POSITION(ARRAY['1','2','3'],'2')
Type |
Query Statement |
Returned Result |
---|---|---|
Scenario |
ARRAY_POSITION(ARRAY['1','2','3'],'2') |
2 |
cardinality
This function calculates the number of elements in an array. The elements must be of the same parameter type.
Syntax: cardinality(expr)
Parameter |
Description |
Type |
Mandatory |
---|---|---|---|
expr |
Original array. |
Array (string/number) |
Yes |
Return value type: integer
Example: SELECT CARDINALITY(ARRAY['1','2','3'])
Type |
Query Statement |
Returned Result |
---|---|---|
Scenario |
CARDINALITY(ARRAY['1','2','3']) |
3 |
mv_length
This function calculates the number of elements in an array. It is equivalent to the cardinality function.
Syntax: mv_length(expr)
Parameter |
Description |
Type |
Mandatory |
---|---|---|---|
expr |
Original array. |
Array (string/number) |
Yes |
Return value type: integer
Example: SELECT MV_LENGTH(ARRAY['1','2','3'])
Type |
Query Statement |
Returned Result |
---|---|---|
Scenario |
MV_LENGTH (ARRAY['1','2','3']) |
3 |
contains
This function determines whether an array contains a specified element. If the array contains the element, true is returned.
Syntax: contains(expr, ele)
Parameter |
Description |
Type |
Mandatory |
---|---|---|---|
expr |
Original array. |
Array (string/number) |
Yes |
ele |
Specified element. |
String/Number. The type must be the same as that of the element in the array. |
Yes |
Return value type: Boolean
Example: SELECT CONTAINS(ARRAY['1','2'],'1')
Type |
Query Statement |
Returned Result |
---|---|---|
Scenario |
CONTAINS(ARRAY['1','2'],'1') |
true |
mv_contains
This function determines whether an array contains a specified element. If the array contains the element, true is returned. This function is equivalent to the contains function.
Syntax: mv_contains(expr, ele)
Parameter |
Description |
Type |
Mandatory |
---|---|---|---|
expr |
Original array. |
Array (string/number) |
Yes |
ele |
Specified element. |
String/Number. The type must be the same as that of the element in the array. |
Yes |
Return value type: Boolean
Example: SELECT MV_CONTAINS(ARRAY['1','2'],'1')
Type |
Query Statement |
Returned Result |
---|---|---|
Scenario |
MV_CONTAINS(ARRAY['1','2'],'1') |
true |
mv_prepend
This function adds a specified element to the beginning of an array.
Syntax: mv_prepend(expr, arr)
Parameter |
Description |
Type |
Mandatory |
---|---|---|---|
expr |
Specified element. |
String/Number. The type must be the same as that of the element in the array. |
Yes |
arr |
Original array. |
Array (string/number) |
Yes |
Return value type: array
Example: SELECT MV_PREPEND('1', ARRAY ['1','2'])
Type |
Query Statement |
Returned Result |
---|---|---|
Scenario |
MV_PREPEND ('1', ARRAY['1','2']) |
["1","1","2"] |
mv_append
This function adds a specified element to the end of an array.
Syntax: mv_append(arr, expr)
Parameter |
Description |
Type |
Mandatory |
---|---|---|---|
arr |
Original array. |
Array (string/number) |
Yes |
expr |
Specified element. |
String/Number. The type must be the same as that of the element in the array. |
Yes |
Return value type: array
Example: SELECT MV_APPEND(ARRAY['1','2'],'1')
Type |
Query Statement |
Returned Result |
---|---|---|
Scenario |
MV_APPEND(ARRAY['1','2'], '1') |
["1","2","1"] |
mv_slice
This function returns a portion of an array from index start to index end.
Syntax: mv_slice(arr, start, end)
Parameter |
Description |
Type |
Mandatory |
---|---|---|---|
arr |
Original array. |
Array (string/number) |
Yes |
start |
Start position. |
Integer |
Yes |
end |
End position. |
Integer |
Yes |
Return value type: array
Example: SELECT MV_SLICE(ARRAY['1','2','3','4','5'], 2, 4)
Type |
Query Statement |
Returned Result |
---|---|---|
Scenario |
MV_SLICE(ARRAY['1','2','3','4','5'], 2, 4) |
["3","4"] |
mv_to_string
This function uses a specified delimiter (str) to join all elements of an array (arr) into a string.
Syntax: mv_to_string(arr, str)
Parameter |
Description |
Type |
Mandatory |
---|---|---|---|
arr |
Original array. |
Array (string/number) |
Yes |
str |
Specified delimiter. |
String |
Yes |
Return value type: string
Example: SELECT MV_TO_STRING(ARRAY['1','2','3','4','5'],'-')
Type |
Query Statement |
Returned Result |
---|---|---|
Scenario |
MV_TO_STRING(ARRAY['1','2','3','4','5'],'-') |
1-2-3-4-5 |
string_to_mv
This function uses a specified delimiter (str2) to split a string (str1) into an array.
Syntax: string_to_mv(str1, str2)
Parameter |
Description |
Type |
Mandatory |
---|---|---|---|
str1 |
Original string. |
String |
Yes |
str2 |
Specified delimiter. |
String |
Yes |
Return value type: array
Example: SELECT STRING_TO_MV('1-2-3-4-5','-')
Type |
Query Statement |
Returned Result |
---|---|---|
Scenario |
STRING_TO_MV('1-2-3-4-5','-') |
["1","2","3","4","5"] |
mv_offset
This function returns the array element at the provided 0-based index. If the index is out of bounds, it returns null.
Syntax: mv_offset(arr, index)
Parameter |
Description |
Type |
Mandatory |
---|---|---|---|
arr |
Original array. |
Array (string/number) |
Yes |
index |
Specified index position. |
Integer |
Yes |
Return value type: string/integer/long/Boolean/double
Example: SELECT MV_OFFSET(ARRAY['1','2','3','4','5'], 2)
Type |
Query Statement |
Returned Result |
---|---|---|
Scenario |
MV_OFFSET(ARRAY['1','2','3','4','5'], 2) |
3 |
mv_ordinal
This function returns the array element at the provided 1-based index. If the index is out of bounds, it returns null.
Syntax: mv_ordinal(arr, index)
Parameter |
Description |
Type |
Mandatory |
---|---|---|---|
arr |
Original array. |
Array (string/number) |
Yes |
index |
Specified index position. |
Integer |
Yes |
Return type: string/integer/long/Boolean/double
Example: SELECT MV_ORDINAL(ARRAY['1','2','3','4','5'], 2)
Type |
Query Statement |
Returned Result |
---|---|---|
Scenario |
MV_ORDINAL (ARRAY['1','2','3','4','5'], 2) |
2 |
mv_offset_of
This function returns the 0-based index of expr that appears for the first time in an array. If expr does not appear, it returns -1.
Syntax: mv_offset_of(arr, expr)
Parameter |
Description |
Type |
Mandatory |
---|---|---|---|
arr |
Original array. |
Array (string/number) |
Yes |
expr |
Specified element. |
String/Number. The type must be the same as that of the element in the array. |
Yes |
Return value type: integer
Example: SELECT MV_OFFSET_OF(ARRAY['1','2','3','4','5'], '2')
Type |
Query Statement |
Returned Result |
---|---|---|
Scenario |
MV_OFFSET_OF(ARRAY['1','2','3','4','5'], '2') |
1 |
mv_ordinal_of
This function returns the 1-based index of expr that appears for the first time in an array. If expr does not appear, it returns -1.
Syntax: mv_ordinal_of(arr, expr)
Parameter |
Description |
Type |
Mandatory |
---|---|---|---|
arr |
Original array. |
Array (string/number) |
Yes |
expr |
Specified element. |
String/Number. The type must be the same as that of the element in the array. |
Yes |
Return value type: integer
Example: SELECT MV_ORDINAL_OF(ARRAY['1','2','3','4','5'], '2')
Type |
Query Statement |
Returned Result |
---|---|---|
Scenario |
MV_ORDINAL_OF(ARRAY['1','2','3','4','5'], '2') |
2 |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.