Oracle TRANSLATE function.

Preamble

The Oracle/PLSQL TRANSLATE function replaces the sequence of characters in a string with another character set. However, it replaces one character at a time.

For example, it replaces the first character in string_to_replace with the first character in replacement_string. Then the second character in string_to_replace with the second character in replacement_string will be replaced, and so on.

Oracle/PLSQL TRANSLATE function syntax

TRANSLATE( string1_id, string_to_replace_id, replacement_string_id )

Parameters and function arguments

• string1_id – a string to replace a sequence of characters with another character set.
• string_to_replace_id – string that will be searched in string1.
• replacement_string_id – all characters in string_to_replace will be replaced by the corresponding characters in replacement_string.

The TRANSLATE function returns a string value.

The TRANSLATE function can be used in the following Oracle/PLSQL versions

Let’s consider some examples of the TRANSLATE function and learn how to use the TRANSLATE function in Oracle/PLSQL.

SQL> SELECT TRANSLATE('1tech23', '123', '456') FROM DUAL;

--Result: 4tech56.

SQL> SELECT TRANSLATE('222tech', '2ec', '3it') FROM DUAL;

--Result: 333tith

Difference between Replace and Translate function in oracle

About Enteros

​​​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​