w3resource

Oracle TRANSLATE function

Description

The Oracle TRANSLATE function returns a string with all occurrences of each character specified in another string as 2nd argument replaced by its corresponding character specified in the 3rd argument.

This function does not support CLOB data directly. However, CLOBs can be passed in as arguments through implicit data conversion.

Syntax:

TRANSLATE(expr, from_string, to_string)

The argument from_string can contain more characters than to_string. In this case, the extra characters at the end of from_string have no corresponding characters in to_string. If these extra characters appear in expr, then they are removed from the return value.

An empty string cannot be used for to_string to remove all characters in from_string from the return value.

Parameters:

Name Description Data Types
expr A string or character set which will be displayed after translate VARCHAR2
from_string A string, which all occurrences of each character will find in expr VARCHAR2
to_string A string, which will replace all occurrences of from_string. VARCHAR2

Return Value Type

VARCHAR2

Applies to

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Pictorial Presentation

Oracle Translate function pictorial presentation

Examples: Oracle TRANSLATE function

This example replaces all commas with vertical bars.

SELECT TRANSLATE('comma,delimited,text', ',', '|') AS "New Text" FROM DUAL;

Sample Output:

New Text
--------------------
comma|delimited|text

The following example, will replace multiple characters.

SELECT TRANSLATE('MAP-SSS-PAM-MAN-NUT-TUB', 'AMST', 'BDFL') AS "New Text" FROM DUAL;

Sample Output:

New Text
-----------------------
DBP-FFF-PBD-DBN-NUL-LUB

In the following example, the a will replaced with an e, the h has no complement and is dropped.

SELECT TRANSLATE('So What', 'ah', 'e') AS "New Text"  FROM DUAL;

Sample Output:

New Text
---------
So Wet

In the following example, the capital A is replaced with capital A. The double quote is eliminated because there is no match.

SELECT TRANSLATE('"Replace double quotes "', 'A"', 'A') AS "New Text" FROM DUAL;

Output :

Sample Output:

New Text
------------------------
Replace double quotes

Previous: SUBSTR
Next: TRANSLATE_USING



Share this Tutorial / Exercise on : Facebook and Twitter