DB2 For i, Java, And Regular Expressions

SELECT *
  FROM TABLE(QGPL.re_SPLIT_TF('Line 1
Line 2
Line 3
',X'25')) TEMP;

RowNo	Value
1	Line 1
2	Line 2
3	Line 3

The RowNo column is created by the table function to keep track of the relative position of the text as it is split. Each of the new table functions features this auto numbering column.

Named Capturing Groups

While the concept of RE capturing groups isn’t new, the ability to reference a capturing group by “name” is new to Java in version 7. Loading the Java 7 SDK is a prerequisite to using this code. Named capturing groups provide developers an easy way to extract a piece of information by name instead of ordinal out of plain text. The syntax for naming a capturing group within a RE pattern is as follows:

 (?<name>X) where X is the RE pattern to capture

Say you’re mining text documents for invoice numbers formatted as “INVnnnnn” followed by an order number formatted as “ORDnnnnn” or “CMnnnnn” (CM=credit memo). The sample document text may look like this:

This letter is in regard to invoice INV01432 (from store.com order ORD15423) blah blah blah

You come up with a RegEx pattern to scan for the invoice and order number text as follows:

 (INVd{5}).*?(ORDd{5}|CMd{5})

This pattern has two capturing groups, each enclosed in parenthesis:

1. INVd{5}
2. ORDd{5}|CMd{5}

With Java regular expressions, the text identified by these groups can be accessed by ordinal number (with ordinal zero representing the entire pattern). Specifying a capture group of 1 from the sample document text above will return the invoice number (INV01432). Specifying a capture group of 2 from the sample text will return the order number (ORD15423).

With named capturing, names can now be assigned to each group so you don’t have to worry about referencing ordinals (which can change) when capturing text from a particular pattern. Here, the capturing groups are assigned the names “invoice” and “order”:

 (?<invoice>INVd{5}).*?(?<order>ORDd{5}|CMd{5})

Using the new re_Named_Capture function, you can extract text as follows:

SELECT QGPL.re_Named_Capture(
'This letter is in regard to invoice INV01432 (from store.com order
ORD15423) blah blah blah' ,'(?<invoice>INVd{5}).*?
(?ORDd{5}|CMd{5})','invoice') AS Invoice
FROM SYSIBM.SYSDUMMY1

The query returns the value of the invoice number: INV01432. Substituting the third parameter (capturing group name) with ‘order’, the function will return ORD15423. The capturing group names are case sensitive.

If you’re expecting a capturing group to find only one occurrence, or if you’re only concerned with a specific single occurrence, then the scalar function re_Named_Capture should be used within your code.

However, in some cases, a capturing group may capture many values from the input text. In this case, the table function, re_Named_Capture_TF, will allow the capture of many values with each value returned as a row in a result set. For example, let’s say you need to mine plain text for email addresses. You invent your own or search online to find this RE pattern.

^[_A-Za-z0-9-+]+(.[_A-Za-z0-9-]+)*
      @[A-Za-z0-9-]+(.[A-Za-z0-9]+)*(.[A-Za-z]{2,})$;

The first line of the pattern retrieves the email info and the second line retrieves the domain name. Using this pattern (modified just a tad), this next SQL statement uses re_Named_Capture_TF to extract all of the email addresses out of the text:

SELECT * FROM TABLE(re_Named_Capture_TF(
'Please copy the following people:
joe.shmoe@abc.com. john.doe@xyz.com, jane_pain@ghi.net.  Also, CC:  
mike_sanso@sql.tv.  For more info, visit: http://mydata.net/',

'(?<email>([A-Za-z0-9-+_]+(.[A-Za-z0-9-]+)*@
(?<domain>([A-Za-z0-9-]+(.[A-Za-z0-9]+)*(.[A-Za-z]{2,})))))',

'email')) x

This query returns the following result:

1. joe.shmoe@abc.com
2. john.doe@xyz.com
3. jane_pain@ghi.net
4. mike_sanso@sql.tv

Substituting a different capturing group name, the domain name is returned instead of the full email address:

SELECT * FROM TABLE(re_Named_Capture_TF(
'Please copy the following people:
joe.shmoe@abc.com. john.doe@xyz.com, jane_pain@ghi.net.  Also, CC:  
mike_sanso@sql.tv.  For more info, visit: http://mydata.net/',

'(?<email>([A-Za-z0-9-+_]+(.[A-Za-z0-9-]+)*@
(?<domain>([A-Za-z0-9-]+(.[A-Za-z0-9]+)*(.[A-Za-z]{2,})))))',

'domain')) x

That will return the following list:

1. abc.com
2. xyz.com
3. ghi.net
4. sql.tv

Back Reference–A Cool Feature

One thing to note about named capturing groups in regular expressions is that you can also reference a prior pattern match within an expression. For example, say you need to do a quick and dirty parse of an HTML snippet, trying to find the ALT attribute of all image (<img>) tags. Using the ibm.com/db2 web page’s HTML as an example, here is a typical image tag with the alternate text (ALT) specified:

<img alt=”Announcing DB2 with BLU Acceleration. Accelerate your analytical workload times to the speed of thought.” height=”100″ width=”300″ src=”/software/data/db2/linux-unix-windows/images/blu-300×100.jpg”/>

With HTML attributes, either single or double quotes are allowed as delimiters. The following RE pattern can be used to get the entire image tag, the type of quote delimiter used with the alt attribute and the alt attribute value itself:

(?<imgtag>(<img.*alt=s*(?<quote>("|'))
(?<altvalue>.*?)(k<quote>).*/>))

Of particular importance is the “quote” named capturing group that determines whether a single or double quote is used. A back reference (k<quote>) is specified to make sure the end of the attribute is terminated with the same quote character used at the start. In other words, the k<quote> capturing group says “find the same value identified in the quote capturing group.”

Many text documents (HTML, XML, Java Property Files, etc.) have special tools that can be used to programmatically extract information from them. However, depending on the task at hand, sometimes it’s a burdensome coding chore to break out the “right tool” for what might be a one-time code execution or a job fit for a “simple” database retrieval task. While regular expressions aren’t specifically designed to do XML/HTML parsing, they can nevertheless be used to aid with quick and simple queries.

Keep in mind that these examples use Java’s RE engine. Many other RE implementations exist (PHP, .NET, C, etc.) but they’re not always 100 percent compatible.

In summary, named capturing groups are handy because you can embed several capturing instructions within a single pattern and pull out the piece you need by name. Further, using table functions will allow queries to pull all values from a RE operation and return them in a result set. Regular expressions can parse and validate all kinds of text including phone numbers, mailing addresses, serial number formats, email addresses, etc., so integrate them in your applications and reap the benefits of this powerful text processing tool.

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.

RELATED STORIES

Empower SQL with Java User-Defined Functions

Staggering SQL String Handling with Regular Expressions

Named Capturing Groups feature in Java 7

Configure Java default version on the IBM i (Author’s Note: Following these steps will allow you to make Java 7 the default JDK on IBM i and allow DB2 to make use of it.)

Regular Expression Test Page for Java (Author’s Note: This page offers a free utility to test your Java based regular expression patterns, which is very helpful for RegEx newbies.)

---

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot