Beginner Series, Database, Oracle, REGEXP_LIKE, Regular Expressions

Pattern Matching – Regular Expressions Part 2: REGEXP_LIKE

caret1

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.

Q1 results Q1

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:

Picture1 Picture2

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”

Picture3

 

 

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

dbaOnTap

2 Comments on “Pattern Matching – Regular Expressions Part 2: REGEXP_LIKE