Oracle TRANSLATE() Function

Oracle TRANSLATE() is a built-in function that translates a given string based on a specified translation relationship and returns the translated string.

Oracle `TRANSLATE()` Syntax

Here is the syntax for the Oracle TRANSLATE() function:

TRANSLATE(string, from_string, to_string)

Parameters

string: Required. The string to be translated.
from_set: Required. A string that is a set of characters to be translated.
to_set: Required. A string that is the set of characters to be translated to. Characters in to_set correspond one-to-one to characters in from_set to form the translation relationship.

Return Value

The Oracle TRANSLATE() function returns a translated string that translates all characters specified in from_set in string to the corresponding characters specified in to_set.

If any of the parameters is NULL, TRANSLATE() returns NULL.

Oracle `TRANSLATE()` Examples

Here are some examples that demonstrate the usage of the Oracle TRANSLATE() function.

Basic Usage

This example shows how to use the Oracle TRANSLATE() function to translate a string:

SELECT
    TRANSLATE('xabcdef', 'abcd', '123')
      AS "TRANSLATE('xabcdef', 'abcd', '123')"
FROM dual;

Output:

TRANSLATE('xabcdef', 'abcd', '123')
______________________________________
x123ef

Let’s take a look at the execution process of TRANSLATE('xabcdef', 'abcd', '123'):

from_set is abcd, which tells us that the four characters a, b, c, d are to be translated.
to_set is 123, and from_set and to_set establish the following translation relationship:
- a is translated to 1
- b is translated to 2
- c is translated to 3
- d is translated to '', i.e. the empty string
The translation process for the string 'xabcdef' is as follows:
1. x is not in from_set, so it is retained.
2. a is in from_set, so it is translated to 1.
3. b is in from_set, so it is translated to 2.
4. c is in from_set, so it is translated to 3.
5. d is in from_set, so it is translated to ''.
6. e is not in from_set, so it is retained.
7. f is not in from_set, so it is retained.
The result of the translation is: x123ef.

NULL Parameters

If any of the parameters is NULL, TRANSLATE() returns NULL.

SET NULL 'NULL';
SELECT
    TRANSLATE(NULL, 'A', 'B') Result1,
    TRANSLATE('A', NULL, 'B') Result2,
    TRANSLATE('A', 'B', NULL) Result3
FROM dual;

Output:

RESULT1    RESULT2    RESULT3
__________ __________ __________
NULL       NULL       NULL

In this example, we use the statement SET NULL 'NULL'; to display NULL values as the string 'NULL'.

Conclusion

Oracle TRANSLATE() is a built-in function that translates a given string according to a given translation relationship and returns the translated string.

Oracle TRANSLATE() Syntax