Skip to main content

substring

1. SUBSTRING Function​

The SUBSTRING function in H2 database is used to extract a substring from a given string.

2. Syntax​

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

SUBSTRING(string, start, length)

Arguments​

  • string: The input string from which the substring needs to be extracted.
  • start: The position at which the substring extraction should start. The position is 1-based, meaning the first character of the string is at position 1.
  • length: The length of the substring to be extracted. If not specified, the substring will include all characters from the start position to the end of the string.

Return​

  • The SUBSTRING function returns the extracted substring as a string.

3. Notes​

  • The SUBSTRING function in H2 database allows you to extract a portion of a string based on the specified start position and length.
  • If the start position is negative or exceeds the length of the string, the SUBSTRING function will return an empty string.
  • If the length is negative, the SUBSTRING function will return NULL.
  • Remember to use the correct syntax and datatype while using the SUBSTRING function to avoid errors.

4. Examples​

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

Example 1 - Extracting a substring from a given string:​

SELECT SUBSTRING('Hello World', 7, 5) AS substring;

Output:

substring
---------
World

Example 2 - Extracting a substring from a column:​

CREATE TABLE words (
  id INT PRIMARY KEY,
  word VARCHAR(20)
);

INSERT INTO words VALUES (1, 'Database'), (2, 'SQL'), (3, 'H2 Database');

SELECT id, word, SUBSTRING(word, 3) AS substring
FROM words;

Output:

id |    word     | substring
---+-------------+------------
1  | Database    | tabase
2  | SQL         | L
3  | H2 Database | Database

Example 3 - Extracting a substring with a specified length:​

SELECT SUBSTRING('Hello World', 7, 3) AS substring;

Output:

substring
---------
Wor
  • left - Extract leftmost characters
  • right - Extract rightmost characters