Oracle Replace() Function.

How to Replace a Part of a String in Oracle Table Column?

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • SQL
  • 3 mins read

Here I am giving some examples of Oracle Database Replace() function to replace a part of a string in a table column.

To demonstrate this, we will create a dummy table EMP_DEPT and will insert some data as follows:

Create a Table

CREATE TABLE EMP_DEPT
(
  DEPTNO  NUMBER(4),
  DNAME   VARCHAR2(100)
)
/

Insert Data

I have added the string (@department.) intentionally to department name (dname) column.

SET DEFINE OFF;
Insert into EMP_DEPT
   (DEPTNO, DNAME)
 Values
   (1, 'ACCOUNTS @department.');
Insert into EMP_DEPT
   (DEPTNO, DNAME)
 Values
   (2, 'IT @department.');
Insert into EMP_DEPT
   (DEPTNO, DNAME)
 Values
   (3, 'FINANCE @department.');
Insert into EMP_DEPT
   (DEPTNO, DNAME)
 Values
   (4, 'HR @department.');
Insert into EMP_DEPT
   (DEPTNO, DNAME)
 Values
   (5, 'MARKETTING @department.');
COMMIT;

Query EMP_DEPT Table

SELECT * FROM emp_dept;
DEPTNO	DNAME
1	ACCOUNTS @department.
2	IT @department.
3	FINANCE @department.
4	HR @department.
5	MARKETTING @department.

Oracle Replace Function Examples

The following are three examples, in the first example, you will see how to remove the special characters like (.) and (@) from department name column. In the second example, we will change the department with Dept, and in the third example, we will remove the string Dept and any trailing spaces from the right.

Remove Special Characters from a String of a Table Column

UPDATE emp_dept
   SET dname = REPLACE (REPLACE (dname, '.', NULL), '@', NULL);
COMMIT;

In the above example, it will remove the dot (.) and at the rate sign (@) from table emp_dept column dname. We used the Replace function twice to remove two special characters if there is a single special character need to replace then use it once only.

Now if you will query the table emp_dept the output would be:

SELECT * FROM emp_dept;
DEPTNO	DNAME
1	ACCOUNTS department
2	IT department
3	FINANCE department
4	HR department
5	MARKETTING department

Replace department to Dept Example

UPDATE emp_dept
   SET dname = REPLACE (dname, 'department', 'Dept');
COMMIT;

The above update statement will replace the string department with Dept in the dname column.

Query the table to see the output:

SELECT * FROM emp_dept;
DEPTNO	DNAME
1	ACCOUNTS Dept
2	IT Dept
3	FINANCE Dept
4	HR Dept
5	MARKETTING Dept

Remove The String Dept from the Dname Column and any Trailing Blanks

UPDATE emp_dept
   SET dname = RTRIM (REPLACE (dname, 'Dept', NULL));
COMMIT;

The above update statement will remove the string Dept and will remove any spaces from the right side in the column dname using the Rtrim() function.

Query the Table

SELECT * FROM emp_dept;
DEPTNO	DNAME
1	ACCOUNTS
2	IT
3	FINANCE
4	HR
5	MARKETTING

Data is fully corrected now.

See also: