Oracle Regular Expressions Pocket Reference

Tutorial

A regular expression (often known as a regex) is a sequence of characters that describe a pattern in text. Regular expressions use a syntax that has evolved over a number of years, and that is now codified as part of the POSIX standard.

Regular expressions are extremely useful, because they allow you to work with text in terms of patterns. For example, you can use regular expressions to search the park table and identify any park with a description containing text that looks like a phone number. You can then use the same regular expression to extract that phone number from the description.

Note

This tutorial will get you started using regular expressions, but we can only begin to cover the topic in this small book. If you want to learn about regular expressions in depth, see Jeffrey Friedl’s excellent book Mastering Regular Expressions (O’Reilly).

Patterns

The simplest type of pattern is simply an exact string of characters that you are searching for, such as the string in the following WHERE clause:

SELECT *
FROM park
WHERE park_name='Mackinac Island State Park';

However, the string 'Mackinac Island State Park' isn’t what most people think of when you mention the word “pattern.” The expectation is that a pattern will use so-called metacharacters that allow for matches when you know only the general pattern of text you are looking for.

Standard SQL has long had rather limited support for pattern matching in the form of the LIKE predicate. For example, the following query attempts ...