Oracle TRANSLATE Function Explained

1 year ago
Ava Mitchell
2 minutes

The TRANSLATE function in Oracle is used to replace characters in a string. It takes three parameters: the source string, the set of characters to be replaced, and the set of replacement characters.

Here is how it is used:

TRANSLATE(source_string, characters_to_replace, replacement_characters)
  1. source string: The string that needs to be replaced.
  2. characters_to_replace: a collection of characters that will be replaced with the corresponding replacement characters.
  3. replacement_characters: a collection of characters used to replace the characters at corresponding positions in characters_to_replace.

For example, the following query will replace the character “H” with “J,” the character “o” with “i,” and the character “!” with “?”” in the string “Hello, World!”

SELECT TRANSLATE('Hello, World!', 'Ho!', 'Ji?') FROM dual;

The output is “Jelli, W?rld?”

If the length of the source string and character set differ, Oracle will truncate the longer string to match the length of the shorter string. If the replacement character set is an empty string, the characters at the corresponding positions will be deleted.

In addition, if characters_to_replace or replacement_characters contain multiple characters, the TRANSLATE function will replace each character in the source string one by one.

bannerAds