Replace function Oracle

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

Advertisements

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated.

1 thought on “Replace function Oracle”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s