TRANSLATE function replaces a string with another set of characters.TRANSLATE replace characters in sequential order.

Syntax

TRANSLATE(string, from_string, to_string)

Example 1:-

SQL> select translate(‘sqlandplsql’, ‘sql’, ‘sequel’) as str from dual;

STR
———–
seqandpqseq

Oracle first find all occurrence of string ‘sql’ and replaces with ‘sequel’. The above string has two occurrences and replaced both of them.

Example 2:-

SQL> select translate(‘sqlandplsql’, ‘sql’, ‘SQL’) as str from dual;

STR
———–
SQLandpLSQL

Example 3:-

SQL> select translate(‘sqlandplsql’, ‘l’, ‘9’) as str from dual;

STR
———–
sq9andp9sq9

Example 4:-

SQL> select translate(‘sqlandplsql’, ‘sql’, ”) as str from dual;

S
—-
null

If no matches then TRANSLATE returns null.

Related posts:

REPLACE function