REGEXP_SUBSTR - Pattern Matching

View All Scripts Login to Run Script

Script Name REGEXP_SUBSTR - Pattern Matching
Description Checking for valid email IDs using pattern matching. Combines the use of REGEXP_SUBSTR and REGEXP_INSTR.
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 string pattern. In this case, we are checking for the valid email addresses within a table. The [[:alnum:]] looks for alphanumeric characters in a given string. The + sign indicates there are additional alphanumeric characters in the string and followed by \@, more alphanumeric characters, \., some more alphanumeric characters. \@ and \. tells the query parser to exactly look for the character symbols "@" and ".". The \ means escape these two symbols. Now the pattern must match something similar to johndoe@example.com. If it matches, then the email ID is displayed in the result, else a null value is returned.
Query the table columns and match the specified substring
```
SELECT empName, REGEXP_SUBSTR(emailID, '[[:alnum:]]+\@[[:alnum:]]+\.[[:alnum:]]+') "Valid Email" FROM regexp_temp
```
```
EMPNAME Valid Email
John Doe johndoe@example.com
Jane Doe  - 

2 rows selected.
```

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

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 that we just saw above, 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 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