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> )
Copy

Arguments

value_expr

Value to find in array.

array

The ARRAY to search.

Returns

This function returns a value of BOOLEAN type:

  • The function returns TRUE if value_expr is present in array.

  • The function returns FALSE if value_expr is not present in array.

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'));
Copy
+------------------------------------------------------------------+
| ARRAY_CONTAINS('HELLO'::VARIANT, ARRAY_CONSTRUCT('HELLO', 'HI')) |
|------------------------------------------------------------------|
| True                                                             |
+------------------------------------------------------------------+
SELECT ARRAY_CONTAINS('hello'::VARIANT, ARRAY_CONSTRUCT('hola', 'bonjour'));
Copy
+----------------------------------------------------------------------+
| ARRAY_CONTAINS('HELLO'::VARIANT, ARRAY_CONSTRUCT('HOLA', 'BONJOUR')) |
|----------------------------------------------------------------------|
| False                                                                |
+----------------------------------------------------------------------+