The TRANSLATE function replaces a character into the character specified in to_substring if the character exists in the specified string. Correspondence relationship is determined based on the order of characters specified in from_substring and to_substring. Any characters in from_substring that do not have one on one relationship to to_substring are all removed. This function is working like the REPLACE function but the argument of to_substring cannot be omitted in this function.
TRANSLATE( string, from_substring, to_substring )
string :
• character string
• NULL
from_substring :
• character string
• NULL
to_substring :
• character string
• NULL
--it returns NULL when an argument is specified with NULL value
SELECT TRANSLATE('12345abcdeabcde','abcde', NULL);
translate('12345abcdeabcde', 'abcde', null)
======================
NULL
--it translates 'a','b','c','d','e' into '1', '2', '3', '4', '5' respectively
SELECT TRANSLATE('12345abcdeabcde', 'abcde', '12345');
translate('12345abcdeabcde', 'abcde', '12345')
======================
'123451234512345'
--it translates 'a','b','c' into '1', '2', '3' respectively and removes 'd's and 'e's
SELECT TRANSLATE('12345abcdeabcde','abcde', '123');
translate('12345abcdeabcde', 'abcde', '123')
======================
'12345123123'
--it removes 'a's,'b's,'c's,'d's, and 'e's in the string
SELECT TRANSLATE('12345abcdeabcde','abcde', '');
translate('12345abcdeabcde', 'abcde', '')
======================
'12345'
--it only translates 'a','b','c' into '3', '4', '5' respectively
SELECT TRANSLATE('12345abcdeabcde','ABabc', '12345');
translate('12345abcdeabcde', 'ABabc', '12345')
======================
'12345345de345de'