Updating Column with REPLACE
We can use the REPLACE function in several ways. For this, we are going to use it in conjunction with the UPDATE clause to replace the domain of the email from ‘some.com’ to ‘domain.net’.
The following is the query that does the trick of doing the job for us.
UPDATE EMPLOYEE SET email=REPLACE(email, 'some.com', 'domain.net');
The following is the data of the table after executing the above query:
Output:
Explanation:
The email domain for each employee has been updated from ‘some.com‘ to ‘domain.net’. The REPLACE function ensures that occurrences of ‘some.com‘ in the email column are replaced with ‘domain.net‘.
As you can see the email of each employee has changed from user@some.com to user@domain.net.
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.