Skip to main content

regexp_substr

1. REGEXP_SUBSTR Function​

The REGEXP_SUBSTR function in H2 database is used to extract a substring from a string that matches a specified regular expression pattern.

2. Syntax​

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

REGEXP_SUBSTR(input_string, pattern [, occurrence [, match_parameter ]])

Arguments​

  • input_string: The string from which the substring is to be extracted.
  • pattern: The regular expression pattern to match within the input string.
  • occurrence (optional): The occurrence of the match to be returned. If omitted, the default value is 1.
  • match_parameter (optional): The match parameter to specify the matching behavior. It can be one or more of the following values:
    • 'c': Case-sensitive matching.
    • 'i': Case-insensitive matching.
    • 'm': Multiline matching.
    • 'n': Enables newline-sensitive matching.
    • 's': Enables dotall mode (. matches newline characters).
    • 'u': Enables Unicode case folding.
    • 'x': Enables whitespace and comments within patterns.

Return​

  • The REGEXP_SUBSTR function returns the substring that matches the specified regular expression pattern.

3. Notes​

  • The REGEXP_SUBSTR function in H2 database uses the Java regular expression syntax.
  • If the input string or pattern is NULL, the function returns NULL.
  • If the pattern does not match within the input string, the function returns an empty string.
  • The occurrence parameter determines which match to return. If occurrence is 1, the first match is returned. If occurrence is 2, the second match is returned, and so on.
  • The match_parameter is optional and can be used to modify the matching behavior of the regular expression pattern.
  • Remember to use the correct syntax and provide valid regular expression patterns to avoid errors.

4. Examples​

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

Example 1 - Extracting the first occurrence of a substring using a regular expression pattern:​

SELECT REGEXP_SUBSTR('Hello World', 'W[a-z]+') AS substring;

Output:

substring
---------
World

Example 2 - Extracting a substring using a regular expression pattern with multiple occurrences:​

SELECT REGEXP_SUBSTR('abc123def456', '[0-9]+', 2) AS substring;

Output:

substring
---------
456

Example 3 - Extracting a substring using a case-insensitive regular expression pattern:​

SELECT REGEXP_SUBSTR('Hello World', 'WORLD', 1, 'i') AS substring;

Output:

substring
---------
World
  • regexp_replace - Replace substrings matched by a regular expression pattern.
  • regexp_like - Check if a string matches a regular expression pattern.