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:
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.