Oracle SQL - Using the TRANSLATE and REPLACE Functions

Introduction

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.

Demo

SQL>
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 s.com
----------- | ----------- | -------
milk bumket | milk bucket |  bucket

SQL>

Related Topic