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.