Skip to main content

replace

1. REPLACE Function​

The REPLACE function in H2 database is used to replace all occurrences of a specified substring within a string with another substring.

2. Syntax​

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

REPLACE(string, search, replacement)

Arguments​

  • string: The original string in which the replacement is to be performed.
  • search: The substring to be replaced.
  • replacement: The substring to replace the occurrences of the search string.

Return​

  • The REPLACE function returns a new string with all occurrences of the search string replaced with the replacement string.

3. Notes​

  • The REPLACE function in H2 database is case-sensitive. It will only replace exact matches of the search string.
  • If the search string is not found in the original string, the function will return the original string as it is.
  • Both the search and replacement strings can be of any length, including zero length.
  • The REPLACE function can be used on columns, variables, or literals.

4. Examples​

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

Example 1 - Replacing "world" with "universe" in a string:​

SELECT REPLACE('Hello world', 'world', 'universe') AS new_string;

Output:

new_string
----------------
Hello universe

Example 2 - Replacing multiple occurrences of a substring:​

SELECT REPLACE('banana banana banana', 'ana', 'apple') AS new_string;

Output:

new_string
---------------------
bapple bapple bapple

Example 3 - Using REPLACE on a column:​

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

INSERT INTO fruits VALUES (1, 'banana'), (2, 'apple'), (3, 'orange');

SELECT id, REPLACE(name, 'a', 'o') AS new_name
FROM fruits;

Output:

id | new_name
---+----------
1  | bonono
2  | opple
3  | oronge