Skip to content
Related Articles

Related Articles

PLSQL | REPLACE Function
  • Last Updated : 25 Sep, 2019

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 

Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up
Recommended Articles
Page :