Skip to main content

trim_array

1. TRIM_ARRAY Function​

The TRIM_ARRAY function in H2 database is used to remove null elements from an array.

2. Syntax​

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

TRIM_ARRAY(array)

Arguments​

  • array: The array from which null elements need to be removed.

Return​

  • The TRIM_ARRAY function returns a new array with null elements removed.

3. Notes​

  • The TRIM_ARRAY function in H2 database only removes null elements from the array. Non-null elements are preserved in the resulting array.
  • If the input array is empty or contains only null elements, the TRIM_ARRAY function will return an empty array.
  • The order of the elements in the resulting array is the same as the original array, except for the null elements that are removed.

4. Examples​

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

Example 1 - Removing null elements from an array:​

SELECT TRIM_ARRAY(ARRAY[1, NULL, 3, NULL, 5]) AS trimmed_array;

Output:

trimmed_array
-------------
[1, 3, 5]

Example 2 - Removing null elements from an array stored in a column:​

CREATE TABLE arrays (
  id INT PRIMARY KEY,
  elements ARRAY
);

INSERT INTO arrays VALUES (1, ARRAY[1, NULL, 3, NULL, 5]), 
                         (2, ARRAY[NULL, NULL, NULL]),
                         (3, ARRAY[1, 2, 3]);

SELECT id, elements, TRIM_ARRAY(elements) AS trimmed_array
FROM arrays;

Output:

id |     elements      | trimmed_array
---+-------------------+-----------------
1  | [1, NULL, 3, NULL, 5] | [1, 3, 5]
2  | [NULL, NULL, NULL]    | []
3  | [1, 2, 3]             | [1, 2, 3]
  • ARRAY_REMOVE - Remove specific elements from an array
  • ARRAY_LENGTH - Get the length of an array