Oracle 10g Release 1 added support for POSIX regular expressions for matching patterns in text strings. Release 2 extended the initial list of pattern metacharacters, with additional ones found in Perl. If you're developing scripts against Oracle 10gR2, check out these nifty shortcuts for searching text.
First, a brief review: The function REGEXP_LIKE searches a character column for a pattern, returning a Boolean TRUE or FALSE. You can use it in the WHERE clause in SQL SELECT statements to limit rows, or in PL/SQL blocks to check the validity of data. Similar functions—REGEXP_REPLACE, REGEXP_SUBSTR, and REGEXP_INSTR—can all accept POSIX regular expressions in performing other familiar string operations.
Perl adds some abbreviated patterns for the POSIX "class" expressions. For example, in standard POSIX regular expressions, you use the pattern '[[:digit:]]' to represent a single digit from 0-9. In Perl, this is simplified to '\d'. Curly braces indicate a repeat count, so '\d{3}' specifies three digits. To indicate that you want a character that is anything except a digit, the POSIX pattern is '[^[:digit:]]'. But in Perl, it's just '\D'.
Similar shortcuts exist for whitespace: '\s' versus '[[:space:]]' and non-whitespace: '\S' versus '[^[:space:]]'. Listing A shows an example from the sample HR schema.
Perl extends POSIX "greedy" matching operators with "non-greedy" ones by adding the question mark (?) to these operators. For example, the pattern '^0{2,5}' will match a minimum of two leading zeros, or as many as five. But the interpretation is "greedy," trying to match the longest string possible. If the column contains "000000", the pattern will match the first five. However, the pattern '^0{2,5}?' is "non-greedy," matching the shortest string, which in this case are two zeroes. Listing B shows an example using REGEXP_SUBSTR.
Miss a tip?
Check out the Oracle archive, and catch up on our most recent Oracle tips.
Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. Visit Bob's site.
