These two Oracle function TRANSLATE and REPLACE are mainly used to remove special characters from a string.
TRANSLATE replaces individual characters, and REPLACE is meant to search and replace words
please check the sample code for reference.
----------------------------------------------
Code:
select translate('beer bucket','beer','milk') as translate,
replace ('beer bucket','beer','milk') as replace_1,
replace ('beer bucket','beer') as replace_2
from dual;
Code:
SELECT TRANSLATE('CAG-TTT-GAC-ACA-TGG-ATC', ' ACGT', 'GATC') DNA
FROM dual;
Encryption / Decryption
-------------------------
Code:
SELECT TRANSLATE('this is a secret',
'abcdefghijklmnopqrstuvxyz', '0123456789qwertyuiop[kjhbv')
FROM dual;
SELECT TRANSLATE('p78o 8o 0 o42i4p',
'0123456789qwertyuiop[kjhbv', 'abcdefghijklmnopqrstuvxyz')
FROM dual;