Oracle Regular Expressions Pocket Reference [Paperback]

Jonathan Gennick (Author), Peter Linsley (Author)

Book Description

Publication Date: September 5, 2003 | Series: Pocket Reference (O'Reilly)

Support for regular expressions in SQL and PL/SQL is one of the most exciting features of Oracle Database 10G. Oracle has long supported the ANSI-standard LIKE predicate for rudimentary pattern matching, but regular expressions take pattern matching to a new level. They provide a powerful way to select data that matches a pattern, as well as to manipulate, rearrange, and change that data.

This concise pocket guide is part tutorial and part quick-reference. It's suitable for those who have never used regular expressions before, as well as those who have experience with Perl and other languages supporting regular expressions. The book describes Oracle Database 10G's support for regular expressions, including globalization support and differences between Perl's syntax and the POSIX syntax supported by Oracle 10G. It also provides a comprehensive reference, including examples, to all supported regular expression operators, functions, and error messages.

O'Reilly's Pocket References have become a favorite among developers and database administrators everywhere. By providing a wealth of important details in a concise, well-organized format, these handy books deliver just what you need to complete the task at hand. Whether you're using regular expressions for the first time or applying your skills from other languages to the latest version of Oracle, the Oracle Regular Expressions Pocket Reference is the book to have close by.

Editorial Reviews

About the Author

Jonathan Gennick is an O'Reilly & Associates editor specializing in database and programming titles. Prior to joining O'Reilly, Jonathan amassed some 17 years of programming and database management experience. During the latter part of his career he specialized in relational database programming, and more recently in database management. Jonathan got his start with relational database systems in 1990, first working with Ingres, and later with Digital's Rdb software. During that period he developed a fondness for SQL, and for the challenge of applying SQL creatively in ways that leveraged it's set-oriented capabilities. In 1994 Jonathan made the leap to Oracle, and is now often found writing about it. Recent projects include Oracle SQLLoader (O'Reilly & Associates, 2001); Oracle SQLPlus Pocket Reference (O'Reilly & Associates, 2000); Oracle SQL*Plus: The Definitive Guide (O'Reilly & Associates, 1999); More recently, Jonathan has made forays into other database products, coauthoring Transact-SQL Cookbook (O'Reilly & Associates, 2002) and editing Practical PostgreSQL (O'Reilly & Associates, 2002). Jonathan is certified as an Oracle DBA and is a member of MENSA and the Oracle Technology Network. He holds a Bachelor of Arts degree in Information and Computer Science, with a Business Administration minor, from Andrews University in Berrien Springs, Michigan. Jonathan currently resides in Munising, Michigan with his wife Donna and their two children: Jenny and Jeff. Jonathan may be contacted by email at [e-mail], and you can learn more about him personally by visiting his website at http://gennick.com.

Peter Linsley began his career in the computer industry as a localization engineer focusing on QA of the Oracle Database. He later relocated to Oracle headquarters in California where he acted as a Globalization consultant specializing in the database, and was responsible for developing solutions to aid database character set migration to Unicode. Peter first met with regular expressions in a SED class at Napier University of Edinburgh and has been a devoted UNIX command line addict ever since. During his tenure at Oracle, he dreamt of a world where all developers had access to regular expressions and decided to make it a personal mission to ensure that would become a reality for users of Oracle. In 2003, Peter was fortunate enough to realize this dream and landed a pivotal role in the design and implementation of Oracle Regular Expressions.

Product Details

• Paperback: 64 pages
• Publisher: O'Reilly Media; 1st edition (September 5, 2003)
• Language: English
• ISBN-10: 0596006012
• ISBN-13: 978-0596006013
• Product Dimensions: 7.3 x 4 x 0.2 inches
• Shipping Weight: 2.1 ounces (View shipping rates and policies)
• Average Customer Review: 3.8 out of 5 stars  See all reviews (4 customer reviews)
• Amazon Best Sellers Rank: #712,579 in Books (See Top 100 in Books)
  • #43 in Books > 4-for-3 Books > Computers & Internet > Programming

More About the Author

I am an Oracle DBA, computer book editor, and writer residing in Munising, Michigan, and only six

blocks from Lake Superior. If you want to know more about me, please visit http://Gennick.com.

Customer Reviews

Most Helpful Customer Reviews

6 of 7 people found the following review helpful:

5.0 out of 5 stars All you need to use Oracle regular expressions, January 19, 2007

By 

Jan Goyvaerts "regex guru" (Thailand) - See all my reviews
(REAL NAME)   

This review is from: Oracle Regular Expressions Pocket Reference (Paperback)

This pocket-sized 60-page booklet covers only one topic: the regular expression support in Oracle Database 10g. And it covers it well. Though the book's cover says "pocket reference", the book actually contains both a short tutorial and a reference.

The tutorial does a good job of explaining basic regular expression concepts. It doesn't provide a lot of examples or detailed explanations. But if you're apt and don't mind to experiment a little, it'll probably be all you need. Oracle's regular expression flavor is fairly limited, so there aren't many tricky concepts to explain.

The second half of the book is the actual reference. The first part of the reference lists all of the available regular expression tokens, along with a brief but clear description of what the token matches, and one or two examples. The examples use only literal text, so you can try them in Oracle without needing a particular example database.

The second part of the reference describes Oracle's four regular expression functions: REGEXP_INSTR, REGEXP_LIKE, REGEXP_REPLACE and REGEXP_SUBSTR. All the parameters that these four functions accept are fully explained, with clear indications of what all the possible values for these parameters will do. The book shows several examples for each function, again using only literal text. You can easily replace the strings with column names in your actual SQL code.

The final part of the reference lists all 10 error codes that the regexp functions can trigger, along with a one-paragraph description explaining exactly what the error is about (which isn't always obvious from the error messages themselves).

All in all this is an excellent little book. It's probably all you need to successfully use regular expressions with Oracle.

Many people recommend Mastering Regular Expressions to go along with the Oracle book. I disagree. Oracle implements POSIX regular expressions, which are a fairly limited subset of modern Perl-style regular expressions. (Actually, Perl's regex syntax is a significant extension of POSIX.) Mastering Regular Expressions barely mentions POSIX, and never mentions Oracle. It mostly talks about the advanced regex features in Perl, Java, .NET and PHP. If you use any of these languages, buying both Mastering Regular Expressions and Oracle Regular Expressions is certainly a good idea. But if you're a database administrator working solely with Oracle, the Oracle Regular Expressions Pocket Reference is all you need.

2.0 out of 5 stars Not what I expected, December 29, 2011

By 

Gil (Houston, TX) - See all my reviews

Amazon Verified Purchase

This review is from: Oracle Regular Expressions Pocket Reference (Pocket Reference (O'Reilly)) (Kindle Edition)

I was looking for a concise reference/tutorial for using regular expressions, especially for programming in .NET, and bought this without giving it a good overview. This isn't what I expected or wanted.

This book reviews the use of regular expressions in SQL SELECT statements, apparently a new feature in Oracle SQL. It does go over a number of details about regex that will be useful, but I am having to wade through all the SQL stuff.

The error is mine, but I thought I'd give a warning just in case someone else thinks of doing the same thing.

5 of 8 people found the following review helpful:

4.0 out of 5 stars Cool small booklet on new ORACLE 10g feature, November 14, 2003

By 

Gregor Theis (Meerbusch Deutschland) - See all my reviews
(REAL NAME)   

This review is from: Oracle Regular Expressions Pocket Reference (Paperback)

"ORACLE Regular Expressions" is a booklet about the new ORACLE 10g SQL functions that work with regular expressions:

- REGEXP_INSTR
- REGEXP_LIKE
- REGEXP_REPLACE
- REGEXP_SUBSTR

The authors describe the new functions and the ORACLE regular expression dialect (what kind of patterns the regexp engine handles).

The book consists of five chapters:

- Tutorial (an introduction into regular expressions)
- ORACLE's regular epression support (the functions, NLS support, Perl vs. ORACLE regular expressions)
- Regular Expression Quick Reference (explanation of the regexp dialect that ORACLE understands, what metacharacters does what and so on)
- ORACLE Regular Expression Functions (who to call the four functions mentioned above)
- ORACLE Regular Expression Error Messages (the new ORA- errors related to regexps)

Unfortunately I had no access to an ORACLE 10g database at the time of writing this review (ORACLE 10g was not available for download the "normal" users yet), so that I was not able to test the new functions and the examples in this book (although I am looking forward to do this sooon).

I think that "ORACLE Regular Expressions" is not sufficient to learn Regular Expressions from scratch but this was not the goal of this book anyway. If you are new to regular expressions, get J. Friedl's book "Mastering Regular Expressions" (the very best book about regular expressions in general). Afterwards "ORACLE Regular Expressions" will be perfect as a small reference book for the ORACLE regexp implementation.

I liked the comparison between ORACLE and Perl Regexp Support (I am a big Perl fan, and did lots of DBI programs on ORACLE with Perl). Unfortunately this book is missing an example on how to create a function based index to support REGEXP_LIKE queries (the possibility was mentioned) and get no full table scan.

The pocket reference book from O'Reilly does not contain an alphabetic index. However because of the good table of contents and the small size of the book, having an index is not really necessary.