Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Oracle Regular Expressions with  regexp_like parsing

Oracle Database Tips by Donald Burleson

See also: Oracle Regular expressions indexes as a tuning tool

If you are familiar with UNIX or Linux shell scripting, then you are probably already familiar with using the power of regular expressions.  It's a powerful pattern-matching tool that allows you to implement formatting logic rules for Oracle data column values.

But regular expressions are more than just answering simple questions like "Does this field look like a Social Security Number?"  Regular expression can now be incorporated into Oracle SQL and Java, creating a platform for super-powerful tools that border on Artificial Intelligence.  Regular expressions are commonly used to ensure data quality in Oracle database where data is Hoovered (a Hoover is a vacuum) from many remote schemas into a central repository, usually a Decision Support System (DSS).  Examples would be the EDI feeds (of point-of-sale data) from retail stores (e.g. K-Mart) to manufacturers.

Did you know that every time the cashier swipes an individual item across the bar code reader, that a record of that sale is collected and sent to the manufacturer of the product?  If you use a shopper's club card it gets even cooler, because the manufacturer now has data about the "type" of person who has just purchased their widget.

As a tool for pattern matching and parsing, regular expressions are unparalleled in their sheer power, and regular expressions are an integral part of Oracle database management.  Regular expressions are indispensable for data cleansing and data scrubbing, ensuring data consistency within Oracle databases, large and small.

        '^\([[:digit:]]{3}\) [[:digit:]]{3}-[[:digit:]]{4}$')));

This format mask defines a telephone number at a much greater level of detail than most constraints:

^     = This denotes the first character in the string
$     = This is the end of the string

We could also use these wildcards:

'?'   = allows a preceding character to match zero or one time
'+'   = allows a preceding character to match one or more times
.     = Any character (except new-line \n)
*     = 0, 1, or more of the preceding element.
{n}   = Exactly n repetitions of the preceding element.
{n,} = Matches n or more repetitions of the preceding element.
{m,n} = Matches between m and n reps of the preceding element.
[abc] = Character list, matching a, b, or c.
[a-c] = an alphabetic range match allowing a, b or c
|     = The "or" operator, allowing multiple matches

This matches the standard for USA telephone numbers: (e.g. '(212) 555-1212').  But what of you want a pattern that "looks like" a phone number?  If we go with the "xxx-xxxx" mask, we can "loosen" the definition, like this:


This would cause a hit on any matching phone number patterns, but it would miss these phone numbers:

1 (800) EAT-HERE

Let's start our discussion of Oracle regular expressions for data cleansing by looking at the easiest component, the unification of common data formats such as addresses.

For expert Oracle data cleansing support and data scrubbing consulting, use an expert from BC.  We understand the powerful Oracle data unification tools, and we can aid in improving the data quality of any Oracle database, large or small.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.