TRANSLATEPLSQL

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)
FROM table_name;

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

TRANSLATE(‘TECHHONEY’,’ECH’,’ABC’)

Tabc Honay

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