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:
- input_string – It is used to specify the string whose characters you want to replace with another set of characters.
- string_to_replace – It is used to specify the string which needs to be searched for in the input_string.
- 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:
- Oracle 12c
- Oracle 11g
- Oracle 10g
- Oracle 9i
- Oracle 8i
Example-1:
DECLARE Test_String string(25) := '111w3wiki'; BEGIN dbms_output.put_line(REPLACE(Test_String, '1')); END;
Output:
w3wiki
Example-2:
DECLARE Test_String string(25) := '111w3wiki111'; BEGIN dbms_output.put_line(REPLACE(Test_String, '1')); END;
Output:
w3wiki
Example-3:
DECLARE Test_String string(25) := '111w3wiki111'; BEGIN dbms_output.put_line(REPLACE(Test_String, '1', '2')); END;
Output:
222w3wiki222
Example-4:
DECLARE Test_String string(25) := 'Giiksforgiiks'; BEGIN dbms_output.put_line(REPLACE(Test_String, 'i', 'e' )); END;
Output:
w3wiki
Example-5:
DECLARE Test_String string(25) := 'Giiksforgiiks'; BEGIN dbms_output.put_line(REPLACE(Test_String, 'i', ' ' )); END;
Output:
G ksforg ks