Regular Expressions Everywhere

3 Aug 2005
John Garmany
 

Regular Expression have been a part of the UNIX/Linux world from the beginning.  Some of us have been using regular expressions without even knowing it.  If you use Linux/Unix, you have probably used grep.  Grep searches lines for matching patterns using regulare expresions. 

Regular Expressions is a powerful pattern matching tool.

Many developers and DBAs don't use them because get the same thing accomplished with instr() or substr().  The fact is that regular expressions are much more powerful than simple string parsers.  Although this is far too small a column to explain regular expressions, let me just give you a simple example.  If I wanted to search my log file for all entries that contain an oracle error message I would want all the line that start with "ORA-" such as ORA-00600.  The regular expression would be:

ORA-.+

This includes any pattern that starts with ORA- and has one or more none null characters after it (the .+). The period matches any character except null and the + matches one or more of the previous expression.   What if I wanted to match my name and common misspellings.  My name is spelled Garmany but is often spelled garmony or gormany.  This is also not too hard for regular expressions.

[Gg][ao]rm[ao]ny

The brackets define a set that the character can be in.  The above expression will catch all the common misspellings of my name and will match the first letter, capitalized or not.  Regular expression use codes to match patterns. Real quick here is a partial list:

There are more but you start to get the idea.  This stuff is hard!  Actually it isn't hard at all.  It does take some practice.  For a more complete introduction read Alan Wintersteen's article Regular Expressions for Regular Folks which explains how to use regular expressions in data cleansing.

http://www.odtug.com/2005_handouts.htm#app_dev

So where do I find Regular Expressions?  Now, almost everywhere. 

Java

Java introduce regular expression in version 1.4 with the java.util.regex library.  This library provides powerful extensions that make you expression more understandable.  You have the ability to search for white space or non-white space,  words [a-zA-Z0-9_].  It defines classes just as "lower" = [a-z], "XDigit" (HexDigits) = [0-9A-Fa-f] and many more.  All of these classes can be created using standard regular expressions.  For more on the java.util.regex look to Horstmann and Cornell's Core Java2 Volume 1.

Oracle 10g Database

Oracle has included four functions in the database to support regular expressions.

REGEXP_LIKE
REGEXP_INSTR
REGEXP_REPLACE
REGEXP_SUBSTR

They can be used in SQL or PL/SQL.......
where regexp_like (book_title, 'Oracle')

as opposed to......
where book_title like '%Oracle%')

If the book_title column contains the word Oracle, the function returns true.  You can see that with the pattern defining capability of regular expressions you can far exceed the capability of the "LIKE" clause.

For detailed information on learning regular expression and using them in the database see Jonathan Gennick and Peter Linsley's book Oracle Regular Expressions Pocket Reference.

One final note, regular expressions may not be right for your application.  There is some overhead and some of the old functions like instr(), substring() or indexOf() may perform better than regular expressions on simple patterns.

If you are a DBA that inherited the administration of AS10g, this is the book for you.  Written by a DBA for DBAs Oracle Application Server 10g Administration Handbook by Oracle Press. In Book Stores Now!

Need Oracle App Server Support?

I'm now offering personal app server mentoring for Oracle DBAs who must quickly learn the intricate details of OracleAS10g.  You can have an Oracle expert right at your fingertips, anytime day or night.

We work with dozens of App Server Oracle databases every year, so we know exactly how to quickly assist you with any Oracle9iAS or Oracle Application Server 10g question. 

Call 800-766-1884 for a custom evaluation.

Regards,

John Garmany

Burleson Oracle Consulting

Kittrell, NC, USA, 27544
www.dba-oracle.com
www.remote-dba.net