08 Regular Expression Support

[Marley]

Example of Extracting a Substring

In this example, the road names are extracted from the LOCATIONS table. To do this, the contents in the STREET_ADDRESS column that are before the first space are returned using the REGEXP_SUBSTR function. In the expression used in the query '[^ ]+ ':

[ starts the expression
^ indicates NOT
indicates space
] ends the expression
+ indicates 1 or more
indicates space

[Marley]

Replacing Patterns

This example examines examines COUNTRY_NAME. The Oracle database reformats this pattern with a space after each non-null character in the string. The results are shown.

[Marley]

Regular Expressions and Check Constraints

Regular expressions can also be used in check constraints. In this example, a check constraint is added on the EMAIL column of the EMPLOYEES table. This will ensure that only strings containing an @ symbol are accepted. The constraint is tested. The check constraint is violated because the e-mail address does not contain the required symbol. The NOVALIDATE clause ensures that existing data is not checked.

[Marley]

Summary

In this lesson you have learned to use the regular expression support features that have been introduced in Oracle Database 10g.

[Marley]

Practice 8: Overview

This practices covers searching and replacing data using regular expressions.