The REPLACE function replaces a set of characters in a string with another set of characters. The Oracle PL/SQL Replace function is used to replace all occurrences of a specified string with another string in a given string.
Replace syntax
The syntax for the Replace function is as follows:
REPLACE( string, string_to_replace, [ replacement_string ] )
Where “string” is the original string, “string_to_replace” is the string to be replaced, and “replacement_string” is the string to replace the search string with.
For example, the following PL/SQL statement would replace all occurrences of the string “dog” in the string “The dog chased the cat” with the string “cat”:
SELECT REPLACE('The dog chased the cat', 'dog', 'cat') FROM DUAL;
The above statement would return the string “The cat chased the cat”.
The REPLACE function is case-sensitive. If you want to do case-insensitive replacement, you can convert both the original string and the search string to uppercase or lowercase before passing them to the REPLACE function.
It’s important to notice that the replace function affects only the first occurrence of the search_string, if you want to replace all occurrence in the string you could use the REPLACE function in combination with a loop.
Replace example
select replace('123PL/SQL', '123')from dual;
Result: PL/SQL
select replace('123PL/SQL', '123', 'Tutorial ')from dual;
Result: Tutorial PL/SQL
select replace('01book0123', '01')from dual;
Result: book23
select replace('00002014', '0')from dual;
Result: 214
select replace('version_1', '1', '2')from dual;
Result: version_2
