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.
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).
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 ...