 |
|
Regular Expressions and
Expression Filter |
|
|
The Oracle database has a proven capability to store and manage large data volumes effectively,
from the perspective of backup, recovery, and operational excellence. As the size of the data repository
increases (Oracle 10g is now capable of storing 8 exabytes, or 8,388,608 terabytes!), it is important
to support more flexible and efficient ways to access and process the right set of content in
a timely manner.
This chapter introduces two new features in Oracle 10g: regular expressions and the Expression
Filter. Although their names sound similar, these features are quite different in their uses. They are
grouped together in this chapter because they share a common goal: to programmatically process
large sets of data more efficiently and effectively.
Regular expressions are a way to describe text patterns in order to process text programmatically.
They are commonly used outside the database in command-line and operating system tools. This
chapter introduces the basic syntax of regular expressions and how they might be applicable to
database applications. It follows with a description of Oracle’s support for regular expressions
in the database, as well as some insight on the architecture and performance implications of using
them.
Expression Filter supports a different paradigm of data processing to describe a user population’s
interest in expected data. Where a traditional application matches data against expressions in a
where clause, Expression Filter enables the inverse situation by looking for expressions that match
a particular data point. This is a unique way to process and route new data against large sets of
existing data.
Both regular expressions and Expression Filter open up the possibilities to different classes of
database processing that would be cumbersome to implement otherwise. Master these features
and you will quickly find a use for them in an existing application or project.Regular Expressions
Consider the task of processing a set of text data, either structured (for example, the contents of a large
varchar2 column) or unstructured (for example, an incoming XML stream over HTTP). In the context
of database programming, following are some typical requirements for processing this text:
? Validating the content against a known set of data and optionally remove duplicates
? Matching the content against some search criteria and optionally performing text manipulation
when a match occurs
? Formatting the content into a normalized form. For example, representing a telephone number
as xxx-xxx-xxxx
All of these operations require a pattern to describe the desired content. Patterns are commonly used
when you are working with text, to better organize or visualize the content. Following are some examples
of familiar text patterns:
? An international phone number: +<country code>-<city code>-<phone number>
? A domestic phone number: (xxx) xxx-xxxx
? A Social Security number: xxx-xx-xxxx
Recognizing a pattern visually is one thing, but patterns must be defined programmatically in order be
processed by a machine. A regular expression is a formula to describe complex patterns in text. Regular
expressions open the door to powerful text processing in a standardized, repeatable manner without the
need for extensive custom coding.
Consider the task of processing an input field from a Web form and validating that it is an e-mail
address with the following characteristics:
? It is case-insensitive.
? Starts with a username containing letters, numbers, hyphens, and underscores.
? An @ sign follows the username.
? The host and domain name will consist of two or more groups of letters, numbers, hyphens, or
underscores separated by a period (.).
Doing this programmatically (regardless of the programming language) would require a tangle of string
manipulation code to match the stream of text.
This example only scratches the surface of text processing. With the increasing use of XML as an
exchange medium, regular expressions are useful to validate, parse, and match against XML content.
Life sciences and bio-informatics research are another example where regular expressions play an
important role. Protein and DNA structures are represented as a long text string, and there are many
combinations of these structures in a typical database. Drug discovery and research relies on looking for
and matching specific protein and DNA data efficiently from very large databases of these structures.The National Center for Biotechnology Information ) and the Protein Data
Bank () are examples of research organizations that host protein and DNA databases.
Regular Expression Concepts
Regular expressions originated in automata theory and formal language theory and as such have a
daunting appearance at first. However, the underlying concepts are simple and powerful. It’s the application
of the concepts that takes some getting used to. So let’s start with the concepts.
A regular expression pattern is expressed as a string comprising the following:
? Literal characters. The actual character to search for (e.g., the pattern xyz only matches an
occurrence of “xyz”)
? Meta-characters. Operators that specify algorithms to apply during the search (for example, the
pattern ^xyz will only match a line that starts with the string “xyz” and no other occurrences)
Common Meta-Characters
A summary of commonly used meta-characters is provided in the following table, together with some
examples. For simpler referencing, the entries in the table have been classified into the role the metacharacter
plays in the pattern.SapereOnLine |
|
|
|
|
|
