Regular Expressions with HCL Informix

                                                                                                            Updated: 8/30/18
​
SQL databases have supported the LIKE operator for a very long time and it will allow you to compare a string to a pattern and has wildcard characters to match any single character or zero or more characters.  HCL Informix also supports the MATCHES operator that give you some more wildcard matching options.  Similar to the LIKE predicate, MATCHES has support for wildcard characters to match any single character or zero or more characters.  It also has the ability to match single character from a set or range of characters or match a single character that is not in a set or range of characters.  These are both examples of simple regular expressions.

 
Many of our Linux or Unix users know that the operating system provides very powerful pattern matching with the grep family of commands.  These regular expressions have been standardized in POSIX 1003.2 standard.  The same regular expressions ability is supported in many application and text editors.
 
With later releases 12.10, HCL Informix gives user this same capability.  Over and above LIKES and MATCHES, a user can search for text at the start or end of the string, match alternate expressions, match, match one expressions to name a few.  All with ISO Latin or UTF-8 based character sets.  In addition, support includes the ability to replace a string that matches a regular expression, split a string where it matches a regular expression, or to extract each matching string.
 
For example, it's easy to specify a regular expression for two words in English that are pronounced the same but have different spellings and definitions, in this case 'wood' or 'would':  'wo[ou]l?d'

​This pattern looks for the pattern starting with the text 'wo' followed by either a 'o' or a 'u' and then followed by either 1 or 0 'l' characters and ending in a 'd'.
 
As well as simple match, regular expression supports the ability to replace a string that matches a regular expression
 
In this example, you want to have a web-based search engine that returns search matches in bold using the "and" HTML tags. Furthermore, you want to make the entire word bold in which the match was found. The regular expression in the example below looks for a word in which "she" or "She" occurs, then replaces the matched text with itself (&), enclosed by "and" HTML tags:

The result displayed on a web page looks like this:
 
She sells seashells on the seashore. The seashells she sells are seashore seashells.
 
For more information, see the HCL Informix InfoCenter link or my presentation at IIUG 2017 titled: "B02: What is a Pattern! Using Regular Expressions in 12.10"

Mark Ashworth
Extensibility Architect
HCL Informix 
Connect with me on LinkedIn and Twitter

Informix is a trademark of IBM Corporation in at least one jurisdiction and is used under license.