Skip to main content

array_contains

1. ARRAY_CONTAINS Function​

The ARRAY_CONTAINS function in H2 database is used to check if an array contains a specific value.

2. Syntax​

The syntax for the ARRAY_CONTAINS function in H2 database is as follows:

ARRAY_CONTAINS(array, value)

Arguments​

  • array: The array to search within. It should be an ARRAY data type.
  • value: The value to check for existence within the array. It can be of any data type.

Return​

  • The ARRAY_CONTAINS function returns a boolean value - TRUE if the array contains the specified value, FALSE otherwise.

3. Notes​

  • The ARRAY_CONTAINS function in H2 database only works with arrays. It does not support checking for the existence of a value within other data structures like lists or strings.
  • If the input array is NULL, the function will return NULL.
  • The function performs a case-sensitive comparison for strings.

4. Examples​

Here are a few examples demonstrating the usage of the ARRAY_CONTAINS function in H2 database:

Example 1 - Checking if an array contains a specific value:​

SELECT ARRAY_CONTAINS(ARRAY['apple', 'banana', 'cherry'], 'banana') AS contains;

Output:

contains
---------
TRUE

Example 2 - Checking if an array contains a value stored in a column:​

CREATE TABLE fruits (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  tags ARRAY
);

INSERT INTO fruits VALUES (1, 'apple', ARRAY['red', 'sweet']), (2, 'banana', ARRAY['yellow']), (3, 'orange', ARRAY['orange']);

SELECT id, name, ARRAY_CONTAINS(tags, 'red') AS has_red_tag
FROM fruits;

Output:

id |  name  | has_red_tag
---+--------+------------
1  | apple  | TRUE
2  | banana | FALSE
3  | orange | FALSE
  • ARRAY_LENGTH - Get the length of an array
  • array_slice - Extract a portion of an array