TRANSLATE

TRANSLATE is an Oracle SQL function that will convert a sequence of characters in a string (the match list) to the corresponding characters in a second sequence of characters (the replacement list). Note that it replaces a single character at a time according to position. The first character in the match list is replaced by the first character in the replacement list. The second character with the second, etc. If there are characters left in the match list that do not have positional equivalents in the replacements list, they are deleted.

Examples

Replace all spaces with underscores:

SELECT translate('I am here',' ','_') FROM dual;
TRANSLATE
---------
I_am_here

Replace vertical bars with commas:

SELECT TRANSLATE('field1|field2|field3', '|', ',') FROM dual
TRANSLATE('FIELD1|FI
--------------------
field1,field2,field3

Remove spaces from a string. Note that the replacement list cannot be NULL, hence the dummy character, $. The space is not in the replacement list, and is hence deleted.

SELECT translate('I am here','$ ','$') FROM dual;
TRANSLA
-------
Iamhere

Replace accented characters with its base characters:

SELECT translate('aàeéêèiñoô','àéêèñô','aeeeno') FROM dual;
TRANSLATE('A??E?
----------------
aaaeaaaaaaiaaoaa

Also see

• REPLACE, the normal string replace function.
• REGEXP REPLACE, regular expression replace function.