Skip to main content

array_contains_seq

Description​

Checks whether all the elements of array2 appear in array1 in the same exact order. Therefore, the function will return 1, if and only if array1 = prefix + array2 + suffix.

For example:

  • select array_contains_seq([1,2,3,4], [1,2,3]); returns 1.
  • select array_contains_seq([1,2,3,4], [4,3]); returns 0.

This function is supported from v3.3 onwards.

Syntax​

BOOLEAN array_contains_all(arr1, arr2)

Parameters​

arr: the two arrays to compare. This syntax checks whether arr2 is a subset of arr1 and in the same exact order.

The data types of elements in the two arrays must be the same. For the data types of array elements supported by StarRocks, see ARRAY.

Return value​

Returns a value of the BOOLEAN type.

  • 1 is returned if arr2 is a subset of arr1 and the elements in arr2 observe the same order as those in arr1. Otherwise, 0 is returned.
  • An empty array is a subset of any array. Therefore, 1 is returned if arr2 is empty but arr1 is a valid array.
  • NULL is returned if any input array is NULL.
  • Nulls in arrays are processed as normal values. For example, SELECT array_contains_seq([1, 2, NULL, 3, 4], [2,3]) will return 0. However, SELECT array_contains_seq([1, 2, NULL, 3, 4], [2,NULL,3]) will return 1.

Examples​


MySQL > select array_contains_seq([1,2,3,4], [1,2,3]);
+---------------------------------------------+
| array_contains_seq([1, 2, 3, 4], [1, 2, 3]) |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+

MySQL > select array_contains_seq([1,2,3,4], [3,2]);
+------------------------------------------+
| array_contains_seq([1, 2, 3, 4], [3, 2]) |
+------------------------------------------+
|                                        0 |
+------------------------------------------+

MySQL > select array_contains_seq([1, 2, NULL, 3, 4], ['a']);
+-----------------------------------------------+
| array_contains_all([1, 2, NULL, 3, 4], ['a']) |
+-----------------------------------------------+
|                                             0 |
+-----------------------------------------------+

MySQL > select array_contains_seq([1,2,3,4,null], null);
+------------------------------------------+
| array_contains([1, 2, 3, 4, NULL], NULL) |
+------------------------------------------+
|                                     NULL |
+------------------------------------------+

MySQL > select array_contains_seq([1,2,3,4], []);
+--------------------------------------+
| array_contains_seq([1, 2, 3, 4], []) |
+--------------------------------------+
|                                    1 |
+--------------------------------------+