PL/SQL REPLACE String Function
The REPLACE function is used to replace all the occurrences of a substring with some other string.
Syntax:
REPLACE(string, old_substring, new_substring)
Parameters:
- string: The string in which to replace.
- old_substring: The substring to be replaced.
- new_substring: The substring to replace the old substring.
Example of REPLACE() Function
The following query replaces “World” with “w3wiki”
SELECT REPLACE("Hello World!", "World", "w3wiki") as Greeting;
Output:
Explanation:
The given SQL query utilizes the REPLACE function to modify the string “Hello World!” by replacing every occurrence of the substring “World” with “w3wiki“. The resulting output assigned the alias “Greeting“, is “Hello w3wiki!” – a string where the specified substitution has been applied, effectively altering the original greeting.
How to UPDATE and REPLACE Part of a String in PL/SQL?
PL/SQL is a procedural language designed to enable developers to combine the power of procedural language with Oracle SQL. It is developed by Oracle and serves as one of the three key programming languages embedded in the Oracle database, alongside SQL and Java. PL/SQL includes procedural language elements such as conditions and loops and can handle exceptions (run-time errors). It also allows the declaration of constants and variables, procedures, functions, packages, types and variables of those types, and triggers.
In this article, we are going to see how we can update a part of the string with some other value in PL/SQL. Understanding this can enable the developer to manipulate strings in a much more complex fashion in their tables.
Before looking at how we can go about doing the foretold task, let us have a look at the REPLACE() function.