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:
- The function would look at the source string (“Database Star”).
- The function then looks for all occurrences of the first character in the from_string (which is ” ” from within the ” a” parameter).
- The function then replaces all of those occurrences with the first character in the to_string parameter (which is “_” from the “_o” parameter).
- Steps 2 and 3 are repeated for the second character (finding “a” and replacing it with “o”).
- 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.
Great explanation, thanks! The example where to_string is longer than from_string has clarified how I can use it to strip special characters from a string, and compare it to the another table that has the same attribute.
x_stripped := translate(upper(x), ‘01234567689ABCDEFGHIJKLMNOPQRSTUVWXYZ’ || upper(x), ‘01234567689ABCDEFGHIJKLMNOPQRSTUVWXYZ’);
Some initial testing shows the TRANSLATE function is less expensive than REGEX.