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

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
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join