<<<<<<< HEAD trim_array | H2 | SQLDoc
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