Preface
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.
- WHAT single piece of text are we trying to match?
- HOW MANY repetitions of that text are we trying to match?
- WHERE in the source string should we look for a match?
- 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 |