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.

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 “GeeksforGeeks”

SELECT REPLACE("Hello World!", "World", "GeeksforGeeks") as Greeting;

Output:

Replaced 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 “GeeksforGeeks“. The resulting output assigned the alias “Greeting“, is “Hello GeeksforGeeks!” – a string where the specified substitution has been applied, effectively altering the original greeting.

Examples of Updating Column by Replacing Substring in PL/SQL

Let’s start by creating a table and adding some sample data to the table. We create an EMPLOYEE table which contains fields like empId, name, and the email of the person. The following query creates the table:

CREATE TABLE EMPLOYEE (
  empId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL
);

Now that we have the table, let’s add some data to the table. The following query adds three employee records to the table:

INSERT INTO EMPLOYEE VALUES (0001, 'Clark', 'clark@some.com');
INSERT INTO EMPLOYEE VALUES (0002, 'Dave', 'dave@some.com');
INSERT INTO EMPLOYEE VALUES (0003, 'Ava', 'ava@some.com');

Output:

Initial data

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:

Updated data

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.

Conclusion

The UPDATE statement in PL/SQL is a simple way to change specific segments of string values in a single column. This is especially useful if you need to make large-scale updates or make regular changes across many records in your PL/SQL database. Always make sure to back up your data before running any update statement to avoid any unwanted consequences.