Translate & Replace Oracle

TRANSLATE(
str1 VARCHAR2 CHARACTER SET ANY_CS,
src VARCHAR2 CHARACTER SET STR1%CHARSET,
dest VARCHAR2 CHARACTER SET STR1%CHARSET)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;

Example : In this demo a string is first encrypted then decrypted

SELECT TRANSLATE('this is a secret',
'abcdefghijklmnopqrstuvxyz', '0123456789qwertyuiop[kjhbv')
FROM DUAL;

SELECT TRANSLATE('p78o 8o 0 o42i4p',
'0123456789qwertyuiop[kjhbv', 'abcdefghijklmnopqrstuvxyz')
FROM DUAL;

Example 2 :The a is replaced with an e, the h has no complement and is dropped.
SELECT TRANSLATE('So What', 'ah', 'e')
FROM DUAL;


Overload 1
REPLACE(
srcstr VARCHAR2 CHARACTER SET ANY_CS,
oldsub VARCHAR2 CHARACTER SET SRCSTR%CHARSET,
newsub VARCHAR2 CHARACTER SET SRCSTR%CHARSET := NULL)
RETURN VARCHAR2 CHARACTER SET SRCSTR%CHARSET;

Overload 2
REPLACE(
srcstr CLOB CHARACTER SET ANY_CS,
oldsub CLOB CHARACTER SET SRCSTR%CHARSET,
newsub CLOB CHARACTER SET SRCSTR%CHARSET := NULL)
RETURN CLOB CHARACTER SET SRCSTR%CHARSET;

Example 1: Single Character Replacement
REPLACE(, <'string_to_match'>,<'replacements_string'>)
SELECT REPLACE('So What', 'o', 'ay')
FROM DUAL;

Example 2: Multiple Character Replacement
Replacement of a single character with a phrase
SELECT REPLACE('An idea that is not dangerous is unworthy of being called an idea at all.', 'n idea', ' software program') TRUTH
FROM DUAL;