In Oracle, the TRANSLATE() function allows you to make several single-character, one-to-one substitutions in one operation.

It returns the string provided as a first argument after some characters specified in the second argument are translated into a destination set of characters specified in the third argument.

It’s similar to the REPLACE() function, except that the REPLACE() function replaces the whole string with another string (i.e. not character by character, like TRANSLATE() does).

Syntax

The syntax goes like this:

TRANSLATE(expr, from_string, to_string)

The function returns expr with all occurrences of each character in from_string replaced by its corresponding character in to_string.

Example

Here’s a basic example:

SELECT 
    TRANSLATE('Cat', 'at', 'ow')
FROM DUAL;

Result:

Cow

Here, the a and t characters were replaced with o and w.

In this case, the the REPLACE() function would have produced the same result. Here are the two functions side by side:

SELECT 
  TRANSLATE('Cat', 'at', 'ow') AS "TRANSLATE",
  REPLACE('Cat', 'at', 'ow') AS "REPLACE"
FROM DUAL;

Result:

   TRANSLATE    REPLACE 
____________ __________ 
Cow          Cow       

In this case, the outcome for both functions is the same, but for different reasons.

Here’s what each function did:

• TRANSLATE() replaced a and t (each individual character)
• REPLACE() replaced at (the string)

Mixed Order

This example demonstrates where TRANSLATE() differs from REPLACE(). In this example, I change the order of the characters to be replaced, as well as the characters to replace them:

SELECT 
  TRANSLATE('Cat', 'ta', 'wo') AS "TRANSLATE",
  REPLACE('Cat', 'ta', 'wo') AS "REPLACE"
FROM DUAL;

Result:

   TRANSLATE    REPLACE 
____________ __________ 
Cow          Cat       

In this case only the TRANSLATE() function took effect. This is because this function goes through each character one by one. The REPLACE() function on the other hand, looks for the whole string, in exactly the same order.

A More Powerful Example

This example demonstrates a scenario where the TRANSLATE() function has a significant benefit over the REPLACE() function:

SELECT 
    TRANSLATE('2*[3+4]/{7-2}', '[]{}', '()()')
FROM DUAL;

Result:

2*(3+4)/(7-2)

To get the equivalent result using the REPLACE() function, we’d need to do this:

SELECT
REPLACE
(
      REPLACE
      (
            REPLACE
            (
                  REPLACE
                  (
                        '2*[3+4]/{7-2}',
                        '[',
                        '('
                  ),
                  ']',
                  ')'
            ),
            '{',
            '('
      ),
      '}',
      ')'
)
FROM DUAL;

Result:

2*(3+4)/(7-2)

The above example is based on the Microsoft documentation for the T-SQL TRANSLATE() function, which is similar to the Oracle version.

No Match

If there are no matches, TRANSLATE() returns the string unchanged:

SELECT 
  TRANSLATE('Cat', 'x', 'y')
FROM DUAL;

Result:

Cat

Case Sensitivity

The TRANSLATE() function performs a case-sensitive match:

SELECT 
  TRANSLATE('Cat', 'AT', 'ow')
FROM DUAL;

Result:

Cat

In this example, the case didn’t match, and so the original string was returned unchanged.

Empty Strings

Here’s what happens when an empty string is passed for each given argument:

SET NULL 'null';
SELECT 
  TRANSLATE('Cat', 'at', '') AS r1,
  TRANSLATE('Cat', '', 'ow') AS r2,
  TRANSLATE('', 'at', 'ow') AS r3
FROM DUAL;

Result:

     R1      R2      R3 
_______ _______ _______ 
null    null    null   

By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Space Character

The empty string is not the same as the space character.

Here’s what happens when we change the empty string to a space:

SELECT 
  TRANSLATE('Cat', 'at', ' ') AS r1,
  TRANSLATE('Cat', ' ', 'ow') AS r2,
  TRANSLATE(' ', 'at', 'ow') AS r3
FROM DUAL;

Result:

   R1     R2    R3 
_____ ______ _____ 
C     Cat         

Here’s an example that illustrates how TRANSLATE() and REPLACE() return different results when using the space character:

SELECT 
    TRANSLATE(' ', ' ', 'Cow') AS TRANSLATE,
    REPLACE(' ', ' ', 'Cow') AS REPLACE
FROM DUAL; 

Result:

   TRANSLATE    REPLACE 
____________ __________ 
C            Cow       

Null Arguments

Passing null for any argument returns null:

SET NULL 'null';
SELECT 
    TRANSLATE(null, 'dog', 'cat') AS "1",
    TRANSLATE('Black dog', null, 'cat') AS "2",
    TRANSLATE('Black dog', 'dog', null) AS "3"
FROM DUAL;

Result:

      1       2       3 
_______ _______ _______ 
null    null    null   

Missing Arguments

Calling TRANSLATE() without passing any arguments results in an error:

SELECT TRANSLATE()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT TRANSLATE()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00938: not enough arguments for function
00938. 00000 -  "not enough arguments for function"
*Cause:    
*Action:

Too Many Arguments

And passing too many arguments returns an error:

SELECT TRANSLATE('Cat', 'a', 'b', 'c')
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT TRANSLATE('Cat', 'a', 'b', 'c')
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action: