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

Like this: Like Loading...