Guru: Regular Expressions, Part 2
August 15, 2022
In the first part of this series, I showed how to replace characters in a string using SQL and regular expressions. This time I will show other regular expressions that we can use. The regular expression functions I’m going to show are REGEXP_COUNT and REGEXP_LIKE and the examples are from production programs I recently implemented.
In the first example (Figure 1), I want to get the number of occurrences of a pattern in a string. To make this more interesting, I’m looking for two different patterns in one sting. To achieve this I used REGEXP_COUNT to get the number of times the pattern appears in a string.
Before showing the result of this assertion, let us analyze the arguments used. The first argument is the string to search for. In a production program, it would probably be a variable or a field in a file or table. The second argument tells what I’m looking for in the string. In this case, I am looking for either [CR] Where [GS]. The symbol pipe is used to indicate that this is an “or” statement. The third argument instructs the function to start looking for these patterns in the first position of the string. Finally, the last argument I specified indicates that I want to ignore the case of the letters being searched for. The result of the instruction is shown in Figure 2.
In the following example, I will show the REGEXP_LIKE function (Figure 3). With this function, I check the existence of a pattern with a string. If the pattern is found, the function returns a ‘Y’. If the pattern is not found, the function returns a blank. Let’s look at this function using the same string I used in the first example.
The arguments provided are the same ones I used in REGEXP_COUNT, so they don’t need any further explanation. When I run this statement (Figure 4), a ‘Y’ is returned because the pattern is in the string.
So what if the pattern is not found? I will show this scenario in Figure 5.
When I execute this statement, a blank is returned (Figure 6).
The last example is from a query I wrote to identify customers that have the letters “DBA” at the start of an address field. On the surface, it seems like a simple request, but there’s a twist. During the data entry process, this field may have been filled in as “DBA”, “D/B/A”, (DBA)”, “DBA:” or in other ways. To identify these customers, I wrote the statement shown in Figure 7.
For privacy reasons, I anonymized the library, table name, and field names in the query. I also anonymized the company names in the results (Figure 8).
I hope the examples provided in this series will help you get started with regular expressions. I have found them very useful in my programming and will continue to use them where it makes sense. I’m always happy to have more tools at my disposal.
Guru: Regular Expressions, Part 1
Get your safety education, not hard knocks school
IBM puts the finishing touches on PowerHA for IBM i 7.5