FB

Oracle TRANSLATE Function InformationThe 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, where REPLACE will replace the entire string.

TRANSLATE finds all occurrences of the first character and replaces it 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

The syntax for the TRANSLATE function is:

TRANSLATE ( source_string, from_string, to_string )

 

Parameters

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 “Complete IT Professional”.

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

You would write a function like this:

TRANSLATE("Complete IT Professional", " o", "_a")

The steps it follows are:

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

Let’s see some more Oracle TRANSLATE examples.

Get Your Free PDF: 9 Ways to Improve your Database Skills

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 'Complete IT Professional' AS SAMPLE_TEXT,
TRANSLATE('Complete IT Professional', 'o', 'X') AS TRANSLATE_TEST
FROM dual;

Result:

SAMPLE_TEXT TRANSLATE_TEST
Complete IT Professional CXmplete IT PrXfessiXnal

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

 

Example 2 – Two Characters

This example uses two characters to translate.

SELECT 'Complete IT Professional' AS SAMPLE_TEXT,
TRANSLATE('Complete IT Professional', 'Is', 'SW') AS TRANSLATE_TEST
FROM dual;

Result:

SAMPLE_TEXT TRANSLATE_TEST
Complete IT Professional Complete ST ProfeWWional

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

 

Example 3 – Five Characters

This example uses a longer pair of strings.

SELECT 'Complete IT Professional' AS SAMPLE_TEXT,
TRANSLATE('Complete IT Professional', 'IsC m', 'SWx2q') AS TRANSLATE_TEST
FROM dual;

Result:

SAMPLE_TEXT TRANSLATE_TEST
Complete IT Professional xoqplete2ST2ProfeWWional

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 'Complete IT Professional' AS SAMPLE_TEXT,
TRANSLATE('Complete IT Professional', 'omsi', '12') AS TRANSLATE_TEST
FROM dual;

Result:

SAMPLE_TEXT TRANSLATE_TEST
Complete IT Professional C12plete IT Pr1fe1nal

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 'Complete IT Professional' AS SAMPLE_TEXT,
TRANSLATE('Complete IT Professional', 'omsi', '') AS TRANSLATE_TEST
FROM dual;

Result:

SAMPLE_TEXT TRANSLATE_TEST
Complete IT Professional (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 'Complete IT Professional' AS SAMPLE_TEXT,
TRANSLATE('Complete IT Professional', 'x', 'a') AS TRANSLATE_TEST
FROM dual;

Result:

SAMPLE_TEXT TRANSLATE_TEST
Complete IT Professional Complete IT Professional

No change to the string is made.

 

Example 7 – Compare REPLACE and TRANSLATE

This example shows the difference between REPLACE and TRANSLATE.

SELECT 'Complete IT Professional' AS SAMPLE_TEXT,
REPLACE('Complete IT Professional', 'let', 'sip') AS REPLACE_TEST,
TRANSLATE('Complete IT Professional', 'let', 'sip') AS TRANSLATE_TEST
FROM dual;

Result:

SAMPLE_TEXT REPLACE_TEST TRANSLATE_TEST
Complete IT Professional Compsipe IT Professional Compsipi IT Profissionas

As you can see, the results are slightly different. REPLACE only changed the occurrence of the full string of “let”, where 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.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Image courtesy of digitalart / FreeDigitalPhotos.net

Get Your FREE PDF: 9 Ways to Improve your Database Skills