Oracle SQL - Using the TRANSLATE and REPLACE Functions

  1. HOME
  2. Oracle SQL
  3. Select
  4. Text 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--  ww  w  .jav a2 s.  c  om
----------- | ----------- | -------
milk bumket | milk bucket |  bucket

SQL>

Related Topic