Oracle TRANSLATE Function Explained with Examples

The Oracle TRANSLATE function is a similar function to REPLACE, but it has a few differences. Learn what the differences are and see some examples in this article.

 

Purpose of the Oracle TRANSLATE Function

The Oracle TRANSLATE function replaces a sequence of characters in a string with another sequence of characters.

It’s different to the REPLACE function as TRANSLATE replaces a single character at a time, and REPLACE will replace the entire string.

TRANSLATE finds all occurrences of the first character and replaces them with the first “replacement” character. It then does the same for the second character, and so on, depending on what parameters you’ve given.

 

Syntax and Parameters

The syntax for the TRANSLATE function is:

TRANSLATE ( source_string, from_string, to_string )

The parameters of the TRANSLATE function are:

• source_string (mandatory): This is the string to search within.
• from_string (mandatory): This is the string that will be searched for within source_string.
• to_string (mandatory): This is the string of characters that contain the values of from_string to replace within source_string.

Some notes about this function:

• If a character is in the source_string but not in from_string, it is not replaced.
• The from_string can contain more characters than to_string. If it does, then the extra characters are not considered for replacement, and are removed from the return value
• The to_string value cannot be empty (for example, if you wanted to remove all from_string values from the source_string). If you do this, the TRANSLATE function will return NULL.
• The from_string is case-sensitive. Searching for uppercase characters will not find a match for lower-case characters, and vice versa.
• The to_string is case-sensitive. Characters for replacement will be the exact character you specify.

 

How the TRANSLATE Function Works

It works by replacing individual characters in a sequence.

Let’s say you have the string “Database Star”.

What if you wanted to replace all spaces with underscores, and all occurrences of the letter “a” with a letter “o”?

You would write a function like this:

TRANSLATE("Database Star", " a", "_o")

The steps it follows are:

1. The function would look at the source string (“Database Star”).
2. The function then looks for all occurrences of the first character in the from_string (which is ” ” from within the ” a” parameter).
3. The function then replaces all of those occurrences with the first character in the to_string parameter (which is “_” from the “_o” parameter).
4. Steps 2 and 3 are repeated for the second character (finding “a” and replacing it with “o”).
5. The function then returns the source_string with the changed values. In this case, it would be “Dotobose_Stor”.

Let’s see some more Oracle TRANSLATE examples.

Examples of the TRANSLATE Function

Here are some examples of the TRANSLATE function. I find that examples are the best way for me to learn about code, even with the explanation above.

 

Example 1 – Single Character

This example uses a single character in the TRANSLATE function.

SELECT 'Database Star' as SAMPLE_TEXT,
TRANSLATE('Database Star', 'a', 'X') as TRANSLATE_TEST
FROM dual;

Result:

SAMPLE_TEXT	TRANSLATE_TEST
Database Star	DXtXbAse StXr

The function has replaced all occurrences of “a” with an “X”.

 

Example 2 – Two Characters

This example uses two characters to translate.

SELECT 'Database Star' as SAMPLE_TEXT,
TRANSLATE('Database Star', 'Ds', 'SW') as TRANSLATE_TEST
FROM dual;

Result:

SAMPLE_TEXT	TRANSLATE_TEST
Database Star	SatabaWe Star

The function has replaced all occurrences of “D” with “S”, and replaced “s” with “W”.

 

Example 3 – Five Characters

This example uses a longer pair of strings.

SELECT 'Database Star' as SAMPLE_TEXT,
TRANSLATE('Database Star', 'btr s', 'SWx2q') as TRANSLATE_TEST
FROM dual;

Result:

SAMPLE_TEXT	TRANSLATE_TEST
Database Star	DaWaSaqe2SWax

The result looks very messy, but the translation has worked as expected!

 

Example 4 – To_String is Smaller than From_String

This example shows you what happens if the to_string is smaller than the from_string.

SELECT 'Database Star' as SAMPLE_TEXT,
TRANSLATE('Database Star', 'asbe', '12') as TRANSLATE_TEST
FROM dual;

Result:

SAMPLE_TEXT	TRANSLATE_TEST
Database Star	D1t112 St1r

The translation happens correctly for the “o” and “m” characters, and the remaining characters are removed from the output altogether.

 

Example 5 – To_String is Empty

This example shows you what happens if the to_string is empty.

SELECT 'Database Star' as SAMPLE_TEXT,
TRANSLATE('Database Star', 'a', '') as TRANSLATE_TEST
FROM dual;

Result:

SAMPLE_TEXT	TRANSLATE_TEST
Database STar	(null)

The result is NULL because an empty string is treated as NULL.

 

Example 6 – Character Not Found

This example shows you what happens if the from_string is not found in the source_string.

SELECT 'Database Star' as SAMPLE_TEXT,
TRANSLATE('Database Star', 'x', 'a') as TRANSLATE_TEST
FROM dual;

Result:

SAMPLE_TEXT	TRANSLATE_TEST
Database Star	Database Star

No change to the string is made.

 

Example 7 – Compare REPLACE and TRANSLATE

This example shows the difference between REPLACE and TRANSLATE.

SELECT 'Database Star' as SAMPLE_TEXT,
REPLACE('Database Star', 'ba', 'xo') as REPLACE_TEST,
TRANSLATE('Database Star', 'ba', 'xo') as TRANSLATE_TEST
FROM dual;

Result:

SAMPLE_TEXT	REPLACE_TEST	TRANSLATE_TEST
Database Star	Dataxose Star	Dotoxose Stor

As you can see, the results are slightly different. REPLACE only changed the occurrence of the full string of “ba”, and TRANSLATE changed all occurrences of each individual letter.

 

Similar Functions

Some functions which are similar to the TRANSLATE function are:

• REPLACE – This function replaces the entire string of characters, rather than one at a time like TRANSLATE.
• SUBSTR – This function returns a smaller string from within a larger string.

You can find a full list of Oracle SQL functions here.