Skip to main content

regexp_replace

1. REGEXP_REPLACE Function​

The REGEXP_REPLACE function in H2 database is used to replace substrings in a string that match a specified regular expression pattern.

2. Syntax​

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

REGEXP_REPLACE(input_string, pattern, replacement)

Arguments​

  • input_string: The string in which the replacement needs to be performed.
  • pattern: The regular expression pattern to be matched.
  • replacement: The string to replace the matched pattern.

Return​

  • The REGEXP_REPLACE function returns the modified string after replacing the matching substrings.

3. Notes​

  • The REGEXP_REPLACE function in H2 database uses regular expressions to find and replace substrings. Make sure you are familiar with regular expression syntax.
  • If the input string or pattern is NULL, the function will return NULL.
  • By default, the function replaces all occurrences of the pattern in the input string. To replace only the first occurrence, you can use the optional occurrence parameter.
  • The REGEXP_REPLACE function is case-sensitive. To perform a case-insensitive replacement, you can use the (?i) flag in the pattern.

4. Examples​

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

Example 1 - Replacing all occurrences of a pattern in a string:​

SELECT REGEXP_REPLACE('Hello World', 'o', 'x') AS replaced_string;

Output:

replaced_string
---------------
Hellx Wxrld

Example 2 - Replacing the first occurrence of a pattern in a string:​

SELECT REGEXP_REPLACE('Hello World', 'o', 'x', 1) AS replaced_string;

Output:

replaced_string
---------------
Hellx World

Example 3 - Performing a case-insensitive replacement:​

SELECT REGEXP_REPLACE('Hello World', '(?i)o', 'x') AS replaced_string;

Output:

replaced_string
---------------
Hellx Wxrld
  • regexp_like - Check if a string matches a regular expression pattern.
  • REGEXP_INSTR - Find the position of a regular expression pattern in a string.
  • REGEXP_SUBSTRING - Extract substrings from a string based on a regular expression pattern.