Oracle TRANSLATE Function: A Simple Guide

The TRANSLATE function in Oracle SQL is a powerful tool for performing multiple, single-character substitutions all at once.

It works like a find-and-replace for individual characters. You provide a list of characters to find (from_string) and a corresponding list of characters to replace them with (to_string).

What is the TRANSLATE Function in Oracle?

The TRANSLATE function scans a string and replaces every character it finds in the from_string with the character at the same position in the to_string.

This is different from REPLACE, which substitutes an entire string for another entire string. TRANSLATE works on a one-to-one character basis.

This is useful for:

• "Sanitizing" data by replacing multiple special characters at once.
• Changing a phone number's format (e.g., replacing () with []).
• Removing a set of characters from a string.

TRANSLATE Function Syntax

The syntax for TRANSLATE is as follows:

TRANSLATE(char, from_string, to_string)

Let's break that down:

• char: The original string or column you want to modify (e.g., 'Hello World').
• from_string: The list of characters you want to find (e.g., 'aeiou').
• to_string: The list of characters you want to replace with.
  • The 1st character in from_string is replaced by the 1st character in to_string.
  • The 2nd character in from_string is replaced by the 2nd character in to_string, and so on.

Important Rule: If from_string is longer than to_string, any characters in from_string that don't have a matching character in to_string are removed from the result.

Oracle TRANSLATE Function Examples

Here are two practical examples of how to use TRANSLATE.

Example 1: Replacing Multiple Characters at Once with TRANSLATE

Let's say you want to reformat a phone number, changing the parentheses () to square brackets [] and the dash - to a period ..

Query:

SELECT 
  TRANSLATE('(123)-456-7890', '()-', '[].') AS "Reformatted Phone"
FROM DUAL;

In this query:

• ( is replaced with [
• ) is replaced with ]
• - is replaced with .

Result:

Reformatted Phone
-----------------
[123].456.7890

Example 2: Removing a Set of Characters with TRANSLATE

This is a common "trick" with TRANSLATE. If you want to remove all numbers from a string, you can provide a from_string that includes all digits but a to_string that doesn't.

To do this, we add a "placeholder" character to the front of both strings.

Query:

-- We want to remove all digits '0123456789'
-- We use 'a' as a placeholder.
-- 'a' gets replaced with 'a'
-- '0' gets replaced with nothing (it's removed)
-- '1' gets replaced with nothing (it's removed)
-- ...and so on.

SELECT 
  TRANSLATE('Order #ABC-123-DEF-456', 'a0123456789', 'a') AS "Cleaned String"
FROM DUAL;

Result:

Cleaned String
--------------
Order #ABC--DEF-