Skip to main content

translate

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)

Arguments​

  • 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.

Return​

  • 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;

Output:

translated_string
-----------------
H123, W123rd!

Example 2 - Replacing characters in a column:​

CREATE TABLE messages (
  id INT PRIMARY KEY,
  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;

Output:

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.