Translate function Oracle

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

4 responses to “Translate function Oracle”

  1. Translate is very much used in Oracle. With string Group function. Nice example…..

  2. select translate(‘sqlandplsql’, ‘l’, ‘9’) as str from dual;

    STR
    ———–
    sq9andp9sq9

    but my reqirement only one 9 replace in l
    i.e sq9andplsql i want this answer how to write quary

    1. Try using replace function

  3. Try using replace function

  4. why does

    select TRANSLATE(‘aAbB(425)555-121”2 w”m %@$##& z’
    ,’Z (){}[]/\;:.,-_=+*&^%$#@!””’
    ,’1′) A
    from dual

    return: aAbB4255551212wmz

    ?