REPLACE(): Oracle PL/SQL function Examples and Usage

PL/SQL String Functions: REPLACE()

Exercise 1:

Write a PL/SQL block that replaces all occurrences of the substring 'SA_MAN' with Sales Manager' in the job titles of employees in the employees table. Display the updated job titles.

Sample Solution:

Table: employees

employee_id		integer
first_name		varchar(25)
last_name		varchar(25)
email			archar(25)
phone_number		varchar(15)
hire_date		date
job_id			varchar(25)
salary			integer
commission_pct		decimal(5,2)
manager_id		integer
department_id		integer

PL/SQL Code:

DECLARE
v_job_idemployees.job_id%TYPE;
BEGIN
  FOR emp IN (SELECT job_id FROM employees) LOOP
    IF v_job_id = 'SA_MAN' THEN
v_job_id := 'Sales Manager';
    END IF;
    DBMS_OUTPUT.PUT_LINE('Updated Job ID: ' || v_job_id);
  END LOOP;
END;

Sample Output:

.....
Updated Job ID: ST_CLERK
Updated Job ID: ST_CLERK
Updated Job ID: ST_CLERK
Updated Job ID: Sales Manager
Updated Job ID: Sales Manager
Updated Job ID: Sales Manager
Updated Job ID: Sales Manager
Updated Job ID: Sales Manager
Updated Job ID: SA_REP
Updated Job ID: SA_REP
Updated Job ID: SA_REP
Updated Job ID: SA_REP
Updated Job ID: SA_REP
Updated Job ID: SA_REP

.......

Explanation:

The said code in Oracle's PL/SQL that retrieves each "job_id" from the 'employees' table perform the operation replacement of 'SA_MAN' with 'Sales Manager' and displays the updated job ID for each row.

The variable "v_job_id" of type employees.job_id is declared.

A loop that retrieves each row from the 'employees' table, selecting only the "job_id" column. The loop variable is named "emp" and the "job_id" value assigned to the variable "v_job_id".

Then checks whether the value of "v_job_id" is equal to 'SA_MAN' and if true update the value of "v_job_id" to 'Sales Manager' executes that is it replaces 'SA_MAN' with 'Sales Manager'.

The DBMS_OUTPUT.PUT_LINE concatenates the string 'Updated Job ID: ' with the value of "v_job_id" and displays the result.

Flowchart:

Exercise 2:

Write a PL/SQL block that prompts the user to enter a substring to be replaced and a replacement substring. Use the REPLACE function to replace all occurrences of the entered substring with the replacement substring in the job titles of employees in the employees table. Display the updated job titles.

Table: employees

employee_id		integer
first_name		varchar(25)
last_name		varchar(25)
email			archar(25)
phone_number		varchar(15)
hire_date		date
job_id			varchar(25)
salary			integer
commission_pct		decimal(5,2)
manager_id		integer
department_id		integer

PL/SQL Code:

DECLARE
v_substringVARCHAR2(50);
v_replacementVARCHAR2(50);
v_job_titleemployees.job_id%TYPE;
BEGIN
v_substring := 'SA';
v_replacement := 'Sales';
  FOR emp IN (SELECT job_id FROM employees) LOOP
v_job_title := REPLACE(emp.job_id, v_substring, v_replacement);
    DBMS_OUTPUT.PUT_LINE('Updated Job Title: ' || v_job_title);
  END LOOP;
END;

Sample Output:

.....
Updated Job Title: ST_CLERK
Updated Job Title: ST_CLERK
Updated Job Title: ST_CLERK
Updated Job Title: ST_CLERK
Updated Job Title: Sales_MAN
Updated Job Title: Sales_MAN
Updated Job Title: Sales_MAN
Updated Job Title: Sales_MAN
Updated Job Title: Sales_MAN
Updated Job Title: Sales_REP
Updated Job Title: Sales_REP
Updated Job Title: Sales_REP
Updated Job Title: Sales_REP
Updated Job Title: Sales_REP
Updated Job Title: Sales_REP
Updated Job Title: Sales_REP
Updated Job Title: Sales_REP
Updated Job Title: Sales_REP
......

Explanation:

The said code in Oracle's PL/SQL that loops through each row in the employees table, replaces occurrences of 'SA' in the job_id column with 'Sales', and displays the updated job title for each row.

The variable v_substring is initialized with the value 'SA', and v_replacement is initialized with 'Sales' and the v_job_title of type employees.job_id is declared.

The FOR loop retrieves job_id column of each row from the employees table and the job_id value is stored in the emp.job_id variable.

Inside the loop, the REPLACE function replaces the v_substring value ('SA') with the v_replacement value ('Sales') in the emp.job_id string and the result is assigned to the v_job_title variable.

The DBMS_OUTPUT.PUT_LINE statement displays the updated job title, concatenated with the text 'Updated Job Title:'.

Flowchart:

Exercise 3:

Write a PL/SQL block that replaces all occurrences of the string 'MAN' with 'MANAGER' in the department names of departments in the departments table. Display the updated department names.

Table: departments

department_id			integer
department_name			varchar(25)
manager_id			integer
location_id			integer

PL/SQL Code:

DECLARE
v_department_iddepartments.department_id%TYPE;
v_department_namedepartments.department_name%TYPE;
BEGIN
  FOR dept IN (SELECT department_id, department_name FROM departments) LOOP
v_department_id := dept.department_id;
v_department_name := REPLACE(dept.department_name, 'IT', 'Info.Tech.');
    DBMS_OUTPUT.PUT_LINE('Department ID: ' || v_department_id || ', Updated Department Name: ' || v_department_name);
  END LOOP;
END;

Sample Output:

Department ID: 10, Updated Department Name: Administration
Department ID: 20, Updated Department Name: Marketing
Department ID: 30, Updated Department Name: Purchasing
Department ID: 40, Updated Department Name: Human Resources
Department ID: 50, Updated Department Name: Shipping
Department ID: 60, Updated Department Name: Info.Tech.
Department ID: 70, Updated Department Name: Public Relation
Department ID: 80, Updated Department Name: Sales
Department ID: 90, Updated Department Name: Executive
Department ID: 100, Updated Department Name: Finance
Department ID: 110, Updated Department Name: Accounting

......

Explanation:

The said code in Oracle's PL/SQL that loops through each row in the departments table, replaces occurrences of 'IT' in the department_name column with 'Info.Tech.', and displays the department ID and the updated department name for each row.

The variable v_department_id of the same data type as the departments.department_id and the v_department_name of the same data type as the departments.department_name are declared.

The FOR loop retrieves the department_id and department_name columns of each row from the departments table and the value stored in the v_department_id and v_department_name variable respectively.

Inside the loop, the REPLACE function substitutes the substring 'IT' with 'Info.Tech.' and the result is assigned to the v_department_name variable.

The DBMS_OUTPUT.PUT_LINE statement displays the department ID, concatenated with the text 'Department ID: ', and the updated department name, concatenated with the text 'Updated Department Name: '.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: RPAD() Functions.
Next: TRIM() Function.