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]
5. Related Functions
- ARRAY_REMOVE - Remove specific elements from an array
- ARRAY_LENGTH - Get the length of an array