Replace function repalce the value where ever they was found  string .
Select  Replace ('*THE*','*','TROUBLE') from dual;
Translate  Function  replace the value whereever they  have found one word of the string.
Select Translate('AtheN','AN','the') from dual;