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:
|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.|
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”
|^||Start of the string|
|Ste||Beginning letters of the string|
|(||Starts the group|
|v||Is next character a ‘v’|
|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