REGEXP_INSTR - Pattern Matching

View All Scripts Login to Run Script

Script Name REGEXP_INSTR - Pattern Matching
Description Determine valid email addresses by checking if a specified pattern is present in a string.
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 - empName and emailID.
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.
Inserting rows into 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.
Inserting rows into the regexp_temp table
```
INSERT INTO regexp_temp (empName, emailID) VALUES ('Jane Doe', 'janedoe')
```
```
1 row(s) inserted.
```
Statement 4
The statement queries a specific column values in the table against the specified pattern is present within a string. If it is present, then a "1" is returned, else a "0" is returned. In this case, we are checking for the valid email addresses within a table. The \w looks for a string. The + sign indicates there are additional patterns to check. \@, \., and \w. The \ means escape these two symbols and the strings. Now the pattern must match something similar to johndoe@example.com. Watch the results displayed as either 1 or 0.
Query the table columns and check the specified string is contained in the column value
```
SELECT emailID, REGEXP_INSTR(emailID, '\w+@\w+(\.\w+)+') "IS_A_VALID_EMAIL" FROM regexp_temp
```
```
EMAILID IS_A_VALID_EMAIL
johndoe@example.com 1
janedoe 0

2 rows selected.
```

EMAILID	IS_A_VALID_EMAIL
johndoe@example.com	1
janedoe	0

Statement 5

Query the table columns and match the specified substring and checks for a string pattern within a string value. The statement combines both Substring and Instring REGEXP examples, in one query.

Combined Substring and Instring example

SELECT empName, REGEXP_SUBSTR(emailID, '[[:alnum:]]+\@[[:alnum:]]+\.[[:alnum:]]+') "Valid Email", REGEXP_INSTR(emailID, '\w+@\w+(\.\w+)+') "FIELD_WITH_VALID_EMAIL" FROM regexp_temp


EMPNAME Valid Email FIELD_WITH_VALID_EMAIL
John Doe johndoe@example.com 1
Jane Doe  - 0

2 rows selected.

Statement 6
Drops the table.
```
DROP TABLE regexp_temp
```
```
Table dropped.
```

EMPNAME	Valid Email	FIELD_WITH_VALID_EMAIL
John Doe	johndoe@example.com	1
Jane Doe	-	0

Additional Information

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