Explain the use of Translate Function in Oracle.
The translate function performs a character wise replacement of a string.
The syntax is as follows:
translate( input_string , string1 , string2 )
string1: this is the string which will be looked for in the input string
string2: this is the string that will replace the string1 characters in the input string.
translate('1tech23', '123', '456); would return '4tech56'
Example:
Input String: HELLO123
String1: L21
String2: Ixy
Thus the translate would look like:
translate(‘ HELLO123’, ‘L21’, ‘Ixy’);
And the output that would be returned is: HEIIOyx3
L replaced by I
2 replaced by x
1 replaced by y
This is how you write it:
SQL> select translate('HELLO123','L21','Ixy') from dual;
TRANSLAT
--------
HEIIOyx3
