Oracle TRANSLATE function

Last update on August 06 2024 12:56:09 (UTC/GMT +8 hours)

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.

Uses of Oracle TRANSLATE Function

Character Replacement: Replace all occurrences of specified characters in a string with corresponding characters from another string.

Data Formatting: Transform data formats by replacing specific characters, such as replacing commas with vertical bars.

Multiple Character Substitution: Substitute multiple characters in a string simultaneously.

Character Removal: Remove specific characters from a string when they have no corresponding character in the third argument.

Simplifying Strings: Eliminate unwanted characters, such as quotes, from a string.

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

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