RegexBuddy—The best regex editor and tester for Oracle developers!

Oracle Regular Expressions Pocket Reference by Jonathan Gennick & Peter Linsley (O’Reilly)

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.