Oracle SQL - Function REPLACE Function

  1. HOME
  2. Oracle SQL
  3. Function
  4. REPLACE Function

Introduction

REPLACE function can replace the value(s) found.

The REPLACE function has the following general syntax: 

REPLACE (string, look for, replace with)

All three arguments are necessary.

The 'look for' string will be replaced with the replace with string every time it occurs.

Here is an example:

Demo

SQL>
SQL> SELECT REPLACE ('This is a test',' is ',' may be ')
  2  FROM dual;--  ww w  . j  a  va  2s.c om

REPLACE('THISISATE
------------------
This may be a test

SQL>

Here the 'look for' string is " is ", including the spaces before and after the word is.

If the spaces are not placed around "is", then the "is" in "This" will be replaced along with the word "is", as shown by the following query:

Demo

SQL>
SQL> SELECT REPLACE ('This is a test','is',' may be ')
  2  FROM dual;-- from  w w  w  .  j  ava2  s  .c om

REPLACE('THISISATEST','IS'
--------------------------
Th may be   may be  a test

SQL>

If the look for string is not present, then the replacing does not occur, as shown by the following query:

Demo

SQL>
SQL> SELECT REPLACE ('This is a test','glurg',' may be ')
  2  FROM dual ;-- from w w  w. j  a v a  2 s  .  c o m

REPLACE('THISI
--------------
This is a test

SQL>

Related Topics