Skip to main content

trim

1. TRIM Function​

The TRIM function in H2 database is used to remove specified characters from the beginning and end of a string.

2. Syntax​

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

TRIM([ [ LEADING | TRAILING | BOTH ] [ characters ] FROM ] expression)

Arguments​

  • LEADING, TRAILING, BOTH (optional): Specifies the position from which the characters should be trimmed. The default is BOTH.
  • characters (optional): Specifies the characters that need to be trimmed. If not provided, it removes whitespace characters by default.
  • expression: The string expression from which the characters should be trimmed.

Return​

  • The TRIM function returns the string with specified characters removed from the beginning and end.

3. Notes​

  • The TRIM function in H2 database supports trimming characters from the beginning, end, or both sides of a string.
  • If the LEADING keyword is used, it removes the specified characters only from the beginning of the string.
  • If the TRAILING keyword is used, it removes the specified characters only from the end of the string.
  • If the BOTH keyword is used or not specified, it removes the specified characters from both the beginning and end of the string.
  • If the characters argument is not provided, the TRIM function removes all whitespace characters (spaces, tabs, newlines) by default.
  • If the input string is NULL, the TRIM function will return NULL.
  • Be careful while using the TRIM function as it modifies the original string.

4. Examples​

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

Example 1 - Trimming leading and trailing spaces:​

SELECT TRIM('   Hello, World!   ') AS trimmed_string;

Output:

trimmed_string
--------------
Hello, World!

Example 2 - Trimming specific characters from the end:​

SELECT TRIM(TRAILING '!' FROM 'Hello, World!!!') AS trimmed_string;

Output:

trimmed_string
--------------
Hello, World

Example 3 - Trimming characters from both sides:​

SELECT TRIM(BOTH 'H' FROM 'Hello, World') AS trimmed_string;

Output:

trimmed_string
--------------
ello, World

Example 4 - Trimming leading whitespace from a column:​

CREATE TABLE names (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

INSERT INTO names VALUES (1, '   John');
INSERT INTO names VALUES (2, '   Jane');
INSERT INTO names VALUES (3, 'Alex   ');

SELECT id, TRIM(LEADING ' ' FROM name) AS trimmed_name
FROM names;

Output:

id | trimmed_name
---+-------------
1  | John
2  | Jane
3  | Alex
  • ltrim - Trim leading characters
  • rtrim - Trim trailing characters