array_contains function

Applies to: Databricks SQL Databricks Runtime

Returns true if array contains value.

Syntax

array_contains(array, value)

Arguments

• array: An ARRAY to be searched.
• value: An expression with a type sharing a least common type with the array elements.

Returns

A BOOLEAN. If value is NULL, the result is NULL. If any element in array is NULL, the result is NULL if value is not matched to any other element.

Examples

> SELECT array_contains(array(1, 2, 3), 2);
 true

> SELECT array_contains(array(1, NULL, 3), 2);
 NULL

> SELECT array_contains(array(1, 2, 3), NULL::INT);
 NULL


> SELECT array_contains(array(1, NULL, 3), 2);
 NULL

-- You canot test for a NULL value in an array using this function
> SELECT array_contains(array(1, 2, NULL, 3), NULL::INT);
 NULL

Related

• [ ] (bracket sign) operator
• arrays_overlap function
• array_position function
• SQL data type rules