Regular Expressions

Pattern Matching


In the tutorials to follow we will examine the syntax for specifying patterns using regular expressions. These patterns can be used in any of the REGEXP_ features described earlier in this section. For simplicity, pattern matching will be broken down into four different categories.

  1. WHAT single piece of text are we trying to match?
  2. HOW MANY repetitions of that text are we trying to match?
  3. WHERE in the source string should we look for a match?
  4. WHICH occurrence of the text do we want to match?

Note: When using regular expressions in your own code you will do so using the REGEXP_LIKE, REGEXP_INSTR, REGEXP_REPLACE, and REGEXP_SUBSTR features described in preceeding tutorials. In many of the examples to follow you will not see these features used directly. Instead they are embedded in a view called TEST_RESULTS, which we will select from to see the effect of various pattern and target combinations (initialized with custom procedures like SET_PATTERN, SET_TARGET, and ADD_TARGET). This is done so that we can focus solely on pattern matching and not repeat syntax for features already presented.

The source code for the custom procedures and the view can be found in the Setup topic at the end of this section. The TEST_RESULTS view contains the following columns.

Column Name Description
PATTERN contains the regular expression for the pattern being tested
TARGET contains the target string being examined
MATCH indicates whether or not a match was found using REGEXP_LIKE
MATCHED_VALUE contains the value within the TARGET that matched the PATTERN; the value is extracted using REGEXP_SUBSTR; if a match was not found or a match to an empty string was made MATCHED_VALUE will contain a null (indicated as '(null)')
POSITION indicates the matched value's location within the TARGET as returned by REGEXP_INSTR

Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=]SQL Snippets: Regular Expressions - Pattern Matching[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="">SQL Snippets: Regular Expressions - Pattern Matching</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Regular Expressions - Pattern Matching
  • URL (href):