Oracle SQL - Using the TRANSLATE and REPLACE Functions


The following code shows the difference between the functions REPLACE and TRANSLATE.

TRANSLATE replaces individual characters.

REPLACE offers the option to replace words with other words.

If you use the REPLACE function with only two arguments, instead of three: the function removes words instead of replacing them.


SQL> select translate('coin bucket','coin','milk') as translate
  2  ,      replace  ('coin bucket','coin','milk') as replace_1
  3  ,      replace  ('coin bucket','coin')        as replace_2
  4  from   dual;

TRANSLATE   | REPLACE_1   | REPLACE-- from w w w .j a  v  a 2
----------- | ----------- | -------
milk bumket | milk bucket |  bucket


Related Topic