Replace function replaces the string with another string if it matches. It is a character function in Oracle. Replace function is case-sensitive.

Syntax:-

REPLACE(‘input string’,’find_string’,’replace_string’);

Example:-

1. Below query replaced ‘sql’ with ‘sequel’

SQL>  select replace(‘sqlandplsql’,’sql’,’sequel’) as replace_string from dual;

REPLACE_STRING
—————–
sequelandplsequel

2.  If no match it will return same string

SQL>  select replace(‘sqlandplsql’,’abc’,’sequel’) as replace_string from dual;

REPLACE_STRING
———–
sqlandplsql

3.  If you omit the replace_string, replace function removes the string.

SQL>  select replace(‘sqlandplsql’,’sql’) as replace_string from dual;

REPLACE_STRING
—–
andpl

4. Replace function can directly use in PL/SQL

begin
dbms_output.put_line(replace(‘sqlandplsql’,’sql’,’sequel’));
end;

Also read : Translate function