Open In App

PLSQL | REPLACE Function

Last Updated : 25 Sep, 2019
Comments
Improve
Suggest changes
Like Article
Like
Report
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 

Next Article
Article Tags :

Similar Reads