Translate – Oracle SQL Function

TRANSLATE is similar to the REPLACE function. It differs in that is allows you to make several character replacements in one pass.

TRANSLATE returns a VARCHAR2 string that is arg1 with all instances of characters in the match argument replaced with the corresponding characters in the replace argument.

The match argument can contain several different characters to replace. If you pass an empty string in the replace argument the function will return NULL.

Example:

This example will replace all – characters with a *, and all * characters with a *

SELECT TRANSLATE(‘Convert-dash-and*star’,’-*’,’*-‘) as RESULT from dual;

RESULT
——————
Convert*dash*and-star