Skip to main content


1. TRANSLATE Function

The TRANSLATE function in H2 database is used to replace specified characters in a string with other characters.

2. Syntax

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

TRANSLATE(string, from, to)


  • string: The input string in which characters need to be replaced.
  • from: A string containing the characters to be replaced.
  • to: A string containing the replacement characters.


  • The TRANSLATE function returns a new string where characters from the "from" string are replaced with corresponding characters from the "to" string.

3. Notes

  • The TRANSLATE function in H2 database replaces characters in the input string based on their position. For example, the first character in the "from" string will be replaced with the first character in the "to" string, and so on.
  • If the "from" and "to" strings are of different lengths, the extra characters in the longer string will be ignored.
  • If a character in the input string does not exist in the "from" string, it will remain unchanged in the output string.
  • If the input string is NULL, the TRANSLATE function will return NULL.

4. Examples

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

Example 1 - Replacing characters in a string:

SELECT TRANSLATE('Hello, World!', 'elo', '123') AS translated_string;


H123, W123rd!

Example 2 - Replacing characters in a column:

CREATE TABLE messages (
text VARCHAR(100)

INSERT INTO messages VALUES (1, 'This is a test'), (2, 'Hello, H2 Database!');

SELECT id, text, TRANSLATE(text, 'aeiou', 'AEIOU') AS translated_text
FROM messages;


id |        text         |   translated_text
1 | This is a test | ThIs Is A tEst
2 | Hello, H2 Database! | Hll, H2 Dbts!
  • replace - Replace occurrences of a substring in a string.