- Categories:
Semi-structured and structured data functions (Array/Object)
ARRAY_CONTAINS¶
Returns TRUE if the specified value is found in the specified ARRAY.
Syntax¶
ARRAY_CONTAINS( <value_expr> , <array> )
Arguments¶
value_expr
Value to find in
array
.If
array
is a semi-structured ARRAY,value_expr
must evaluate to a VARIANT.If
array
is a structured ARRAY,value_expr
must evaluate to a type that is comparable to the type of the ARRAY.
array
The ARRAY to search.
Returns¶
This function returns a value of BOOLEAN type:
The function returns TRUE if
value_expr
is present inarray
.The function returns FALSE if
value_expr
is not present inarray
.
Usage notes¶
The function does not support wildcards in
value_expr
. However, you can use the ARRAY_TO_STRING function to convert an array to a string, then search the string with wildcard characters. For example, you can specify wildcards to search the returned string using the [ NOT ] LIKE and REGEXP_LIKE functions.
Examples¶
The following queries use the ARRAY_CONTAINS function:
SELECT ARRAY_CONTAINS('hello'::VARIANT, ARRAY_CONSTRUCT('hello', 'hi'));
+------------------------------------------------------------------+
| ARRAY_CONTAINS('HELLO'::VARIANT, ARRAY_CONSTRUCT('HELLO', 'HI')) |
|------------------------------------------------------------------|
| True |
+------------------------------------------------------------------+
SELECT ARRAY_CONTAINS('hello'::VARIANT, ARRAY_CONSTRUCT('hola', 'bonjour'));
+----------------------------------------------------------------------+
| ARRAY_CONTAINS('HELLO'::VARIANT, ARRAY_CONSTRUCT('HOLA', 'BONJOUR')) |
|----------------------------------------------------------------------|
| False |
+----------------------------------------------------------------------+