Regular expression functions

The regular expression functions identify precise patterns of characters and are useful for data validation, for example, type checks, range checks, and checks for characters that are not allowed. The supported regular expression functions are fully Perl v5 compatible.

A description of how regular expressions operate is beyond the scope of this document. For information, see the many texts that describe how to construct Perl regular expressions.

The regular expression functions apply the regexp pattern recursively to various parts of the input value until one of the following conditions occurs:
  • A match is found. The function returns the match value.
  • A match is not found for the pattern at the current position; the function repeats the search process by using another part of the pattern.

There is a limit on the number of times that the function repeats execution with a new search pattern. In IBM® Netezza® SQL Extensions toolkit Release 1.3 and later, the limit is 10,000,000 recursions, which is the generally accepted default. (In prior releases, the default limit was 10,000.) If the limit is reached before a match is found, the function displays the error ERROR: 0 : Regex exec: Match limit reached, consider rewriting the pattern.

The recursion limit does not limit the input search string; it controls the effort that is spent to match “extreme” patterns such as nested, unlimited patterns. Consider the following example:
https?://([-\w\.]+)+(:\d+)?(/([\w/_\.]*(?\S+)))
The pattern that is highlighted in bold results in a search to match one or more occurrences of (one or more occurrences of alphanumerics). This pattern results in unnecessary recursions that can quickly reach the limit with moderate to large input strings. A better option is to change the search pattern to a more efficient form, as follows:
https?://([-\w\.]+)(:\d+)?(/([\w/_\.]*(?\S+)))