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 


My Personal Notes arrow_drop_up

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

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.