PLSQL | REPLACE Function

The PLSQL REPLACE function is used for replacing a sequence of characters in a string with another set of characters. The REPLACE function accepts three parameters which are input_string, string_to_replace and replacement_string.

The REPLACE function returns input_string with every occurrence of string_to_replace replaced with replacement_string. If replacement_string is omitted or null, then all occurrences of string_to_replace are removed. If string_to_replace is null, then input_string is returned.

Both string_to_replace and replacement_string, as well as input_string, can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is in the same character set as char.

Syntax:

REPLACE( input_string, string_to_replace, replacement_string] )

Parameters Used:

1. input_string – It is used to specify the string whose characters you want to replace with another set of characters.
2. string_to_replace – It is used to specify the string which needs to be searched for in the input_string.
3. replacement_string :It is an optional parameter which is used to specify the replacement string .If the replacement_string parameter is omitted, the REPLACE function simply removes all occurrences of string_to_replace, and returns the resulting string.

Supported Versions of Oracle/PLSQL:

1. Oracle 12c
2. Oracle 11g
3. Oracle 10g
4. Oracle 9i
5. Oracle 8i

Example-1:

DECLARE 
   Test_String string(25) := '111Geeksforgeeks';
   
BEGIN 
   dbms_output.put_line(REPLACE(Test_String, '1')); 
   
END;     

Output:

Geeksforgeeks 

Example-2:

DECLARE 
   Test_String string(25) := '111Geeksforgeeks111';
   
BEGIN 
   dbms_output.put_line(REPLACE(Test_String, '1')); 
   
END;     

Output:

Geeksforgeeks 

Example-3:

DECLARE 
   Test_String string(25) := '111Geeksforgeeks111';
   
BEGIN 
   dbms_output.put_line(REPLACE(Test_String, '1', '2')); 
   
END;     

Output:

222Geeksforgeeks222 

Example-4:

DECLARE 
   Test_String string(25) := 'Giiksforgiiks';
   
BEGIN 
   dbms_output.put_line(REPLACE(Test_String, 'i', 'e' )); 
   
END;    

Output:

Geeksforgeeks 

Example-5:

DECLARE 
   Test_String string(25) := 'Giiksforgiiks';
   
BEGIN 
   dbms_output.put_line(REPLACE(Test_String, 'i', ' ' )); 
   
END;     

Output:

G  ksforg  ks 

Shubrodeep Banerjee

I am a technology enthusiast who has a keen interest in programming I am pursuing Engineering in Computer Science from GEU, Dehradun I like to unwind by watching movies and English sitcomsI have a keen interest in music