The TRANSLATE function in Oracle SQL / PLSQL is used to replace a sequence of characters in a string with another sequence of characters.
The translate function replaces character by character i.e. it will replace the first character in the string to be replaced by the first character in the replacing string.
Syntax for the TRANSLATE function in Oracle SQL / PLSQL is:
SELECT TRANSLATE(string1, string_to_replace, replacement_string)
- String1 is the string in which sequences of characters are to be replaced
- String_to_replace is the sequence of characters to be replaced
- Replacement_string is the string of characters that will replace the string_to_replace
Let’s take an example for understanding:
SELECT TRANSLATE('Tech Honey', 'ech','abc') FROM dual;
The output of the above statement will be:
Here we can see that ‘e’ in ‘Tech Honey’ is replaced by ‘a’ in ‘abc’, ‘c’ is replaced by ‘b’ and ‘h’ is replaced by ‘c’.