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

Advertisements

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated.

4 thoughts on “Translate function Oracle”

  1. why does

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

    return: aAbB4255551212wmz

    ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s