Live SQL

REGEXP_REPLACE - Pattern Matching

View All Scripts Login to Run Script

Script Name REGEXP_REPLACE - Pattern Matching
Description Simple queries that search for a pattern within a string and replace it with another specified pattern.
Area SQL General / Functions
Referenced In Database SQL Language Reference
Contributor Oracle
Created Monday October 05, 2015

Statement 1
The statement creates a table with two fields - employee name and email ID.
Create a new table regexp_temp
```
CREATE TABLE regexp_temp(empName varchar2(20), emailID varchar2(20))
```
```
Table created.
```
Statement 2
The statement inserts a row with values for - employee name and email ID. Note: The email is a valid email address.
Populate the regexp_temp table
```
INSERT INTO regexp_temp (empName, emailID) VALUES ('John Doe', 'johndoe@example.com')
```
```
1 row(s) inserted.
```
Statement 3
The statement inserts a row with values for - employee name and email ID. Note: The email is an invalid email address.
Populate the regexp_temp table
```
INSERT INTO regexp_temp (empName, emailID) VALUES ('Jane Doe', 'janedoe@example.com')
```
```
1 row(s) inserted.
```

Statement 4

Display the table information for employee name.

Query the table with empName

SELECT empName "ALL_EMPLOYEES" FROM regexp_temp


ALL_EMPLOYEES
John Doe
Jane Doe

2 rows selected.

Statement 5
Query and replace the name string for the matching string value. In this case, any name with the string "Jane" will be replaced by the string "John". Watch the result and compare the actual name under empName and the modified name under STRING_REPLACE columns.
Query the table and replace the name string
```
SELECT empName, REGEXP_REPLACE (empName, 'Jane', 'John') "STRING_REPLACE" FROM regexp_temp
```
```
EMPNAME STRING_REPLACE
John Doe John Doe
Jane Doe John Doe

2 rows selected.
```
Statement 6
Query and replace the name string for the matching string value. In this case, any name with the string "John" will be replaced by the string "Jane". Watch the result and compare the actual name under empName and the modified name under STRING_REPLACE columns.
Query the table and replace the name string
```
SELECT empName, REGEXP_REPLACE (empName, 'John', 'Jane') "STRING_REPLACE" FROM regexp_temp
```
```
EMPNAME STRING_REPLACE
John Doe Jane Doe
Jane Doe Jane Doe

2 rows selected.
```
Statement 7
Statement drops the table.
Drop the table
```
DROP TABLE regexp_temp
```
```
Table dropped.
```

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database