Skip to content Skip to main navigation Skip to footer

Regular Expressions

Regular Expressions are a very in-depth and detailed topic. This lesson aims only to give a brief overview of their purpose and use.

Objectives

This lesson covers the following objectives:

  • Describe regular expressions
  • Use regular expressions to search, match, and replace strings in SQL statements
  • Construct and execute regular expressions and check constraints

Purpose

  • Sometimes you have to find or replace a particular piece of text in a column, text string, or document.
  • You already know how to perform simple pattern matching using LIKE and wildcards.
  • Sometimes you might need to look for very complex text strings such as extracting all URLs from a piece of text.
  • Other times you might be asked to do a more complex search such as finding all words whose every second character is a vowel.
  • Regular expressions are a method of describing both simple and complex patterns for searching and manipulating.
  • They are used widely in the computing industry, and are not limited to Oracle.
  • Oracle’s implementation of regular expressions is an extension of the POSIX (Portable Operating System for UNIX) and are, as such, fully compatible with the POSIX standard, as controlled by the Institute of Electrical and Electronics Engineers (IEEE).

Regular Expressions

  • The use of regular expressions is based on the use of meta characters.
    • Meta characters are special characters that have a special meaning, such as a wildcard character, a repeating character, a non matching character, or a range of characters.
    • You can use several predefined meta character symbols in the pattern matching.
    • The next slides list some of the meta characters and provide a brief explanation of each.

META Characters

META Characters

All complete list of META characters with examples of their use can be found at this page.

Regular Expression Examples

  • A simple regular expression is very similar to the wildcard searches you are already familiar with.
  • Let’s take a look at an example: let’s use the dot operator to look for the letter ‘a’ followed by any one character followed by the letter ‘c’.
  • As a regular expression, this would be done as: ‘a.c’.
  • The same expression as a standard SQL wildcard search would be: WHERE column LIKE ‘a_c’.
  • Which of the following strings would match ‘a.c’?
    • ABC‘,
    • abc‘,
    • aqx‘,
    • axc‘,
    • aBc‘,
    • abC
    • Amc
    • amrc
  • The strings in red would match the search string ‘a.c’
  • ‘ABC’, ‘abc’, ‘aqx’, ‘axc’, ‘aBc’, ‘abC’ ‘Amc’ ‘amrc’
  • The other examples fail either due to their having the character in the wrong position or in the wrong case (uppercase not lowercase as specified in the search string).

Regular Expression Functions

  • Oracle provides a set of SQL functions that you can use to search and manipulate strings using regular expressions.
  • You can use these functions on any data type that holds character data such as CHAR, CLOB, and VARCHAR2.
  • A regular expression must be enclosed in single quotation marks.
Regular Expression Functions

Regular Expression Function Examples

  • Assume you were asked to list all employees with a first name of Stephen or Steven.
SELECT first_name, last_name
FROM employees
WHERE REGEXP_LIKE(first_name, '^Ste(v|ph)en$');
  • With regular expressions, you could simply use the REGEXP_LIKE function and the search string: ‘^Ste(v|ph)en$
    • “^” specifies the start of the string that is being searched
    • Uppercase “S”, followed by
    • lowercase “t”, followed by
    • lowercase “e”, followed by
  • With regular expressions, you could simply use the REGEXP_LIKE function and the search string: ‘^Ste(v|ph)en$
    • “(” starts a sub-expression
    • lowercase “v”
    • “|” specifies an OR
    • lowercase “p” followed by Lowercase “h”
    • “)” finishes the group of choices,
    • lowercase “e”
    • lowercase “n”
    • “$” specifies the end of the string that is being searched
  • Example:
SELECT first_name, last_name
FROM employees
WHERE REGEXP_LIKE(first_name, '^Ste(v|ph)en$');
  • Result:
Regular Expression Function Examples
  • Regular expression REPLACE function will replace one string pattern with another.
  • This example looks for an “H” followed by any vowel, and replaces them with two “*” symbols.
SELECT last_name, REGEXP_REPLACE(last_name, '^H(a|e|i|o|u)', '**')
AS "Name changed"
FROM employees;
Regular Expression Function Examples
  • Regular expression COUNT function returns the number of times a pattern appears in a string.
  • This example searches for the sub-expression “ab”
SELECT country_name, REGEXP_COUNT(country_name, '(ab)') AS "Count of 'ab'"
FROM wf_countries
WHERE REGEXP_COUNT(country_name, '(ab)')>0;
Regular Expression Function Examples

Regular Expressions in Check Constraints

  • Regular expressions could also be used as part of the application code to ensure that only valid data is stored in the database.
  • It is possible to include a call to a regular expression function in, for instance, a CHECK constraint.
  • So if you want to ensure that no email addresses without ‘@’ were captured in a table in your database, you could simply add the following check constraint:
ALTER TABLE employees
ADD CONSTRAINT email_addr_chk
CHECK(REGEXP_LIKE(email,'@'));
  • This would ensure that all email addresses include an “@” sign.
  • Using Regular expressions, you can check the format of email addresses more thoroughly to check they are valid.
  • A valid email address would have one or more characters then an @, followed by one or more characters then a . (dot), followed by one or more characters.
Regular Expressions in Check Constraints
  • Syntax definitions:
    • .+ means one or more characters
    • @ an @ symbol
    • . a . (a dot) (here the backslash is an escape character)

Terminology

Key terms used in this lesson included:

  • REGULAR EXPRESSIONS
  • META Characters
  • Subexpressions

Summary

In this lesson, you should have learned how to:

  • Describe regular expressions
  • Use regular expressions to search, match, and replace strings in SQL statements
  • Construct and execute regular expressions and check constraints

Was This Article Helpful?

0
Related Articles
0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *