Last month, I gave a general overview of the Pattern Matching in Regular Expressions. This post will introduce the regular expression function: REGEXP_LIKE in the Oracle Database.  Let’s get started.

What is a Regular Expression Function?

Regular Expression Functions in a nutshell are

• A set of SQL functions used to search and manipulate strings using Regular Expressions
• These functions can be used on any data type that holds character data (CHAR, VARCHAR, CLOB, etc)
• The Regular Expression must be enclosed in single quote marks

These functions consist of the following:

Function name	Description
REGEXP_LIKE	Similar to the LIKE operator but allows for the use of regular expressions in matching
REGEXP_REPLACE	Search and replace text using regular expression pattern
REGEXP_INSTR	Searches for a string using regular expression pattern and returns the position when match is found
REGEXP_SUBSTR	Searches for a string using regular expression pattern and returns the matched substring
REGEXP_COUNT	Returns the number of times a pattern appears in the string.

 

REGEXP_LIKE

Scenario: Return a list of all employees whose first name is Steven or Stephen

Using Simple Pattern Matching with the LIKE operator we need to include some “intelligence” in our query.

 

Of course this is a simple example and there are several ways to attack this.

Here is the same solution using the REGEXP_LIKE function:

Here we took advantage of the Regular Expression ‘OR’ metadata character |, which equates to ‘v’ OR ‘ph’ in the SQL statement predicate. Now a closer look at what’s happening:

“All employees with first name of Steven or Stephen”

 

 

Meta Character	Description
^	Start of the string
Ste	Beginning letters of the string
(	Starts the group
v	Is next character a ‘v’
|	OR
ph	Are next characters ‘ph’
)	End the group
en	Ending letters  of string
$	End of the string

 

Next time we will look at REGEXP_REPLACE