array_contains function

Applies to: check marked yes Databricks SQL check marked yes 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

-- You cannot test for a NULL value in an array using this function.
-- Use the exists function with a lambda instead.
> SELECT array_contains(array(1, 2, NULL, 3), NULL::INT);
 NULL

> SELECT exists(array(1, 2, NULL, 3), x -> x IS NULL);
 true

> SELECT exists(array(1, 2, 3), x -> x IS NULL);
 false
  • Last updated on