Guru: Regular Expressions, Part 1

July 25, 2022 Mike Larsen

In the first part of this series, I will show how to replace characters in a string using SQL and regular expressions. I know I can replace characters using RPG or SQL, and they work great, but there are some cases where I find using regular expressions is a bit easier.

For the examples I’ll show, I’m using the REGEXP_REPLACE function. REGEXP_REPLACE allows me to search for a particular value within a string and replace it with another value.

In the first example (Figure 1), I want to search a string that has both letters and digits and I want to extract the just digits or periods. To accomplish this objective, I use REGEXP_REPLACE to replace characters that are not digits.

Figure 1. Replace characters that are not digits or periods

Let’s break down this example a little further. The first argument specifies the string to be searched. In a production program, this would likely be a variable or a field from a file or table. The second argument indicates what I’m looking for in the string. The carat symbol means ‘not’. ‘0-9.’ means that I want digits zero through nine and the period symbol to be included in my search. The third argument is the replacement character to be used. In this case, if the search finds something, replace it with nothing. Note that it’s not a space. It performs the same duty as trimming, in this case.

To fully explain what this statement is doing, I’m looking for characters in a string that are not zero through nine and not a period. When I run this example in ACS, I get the result shown in Figure 2.

Figure 2. Result of replacing characters that are not digits or periods

The result of the statement is 530, which is exactly what I wanted.

The next example comes from a challenge I came across while sending a parameter from an RPG program to a Python script. The value being passed contained a dollar sign symbol and was causing the value to be truncated when it reached the Python script. I needed to be able to escape the dollar sign symbol in the RPG program prior to sending it to the Python script so the entire contents of the variable would be sent. Figure 3 shows the regular expression I coded to accomplish that task.

Figure 3. Escape the dollar sign symbol in a string

In this example, I’m searching a string for the dollar sign symbol (indicated in the second argument), and I’m going to replace it with two backslashes and a dollar sign that will create the escape character. When I run this, I get the result shown in Figure 4.

Figure 4. Result of escaping the dollar sign symbol

Using that statement in the RPG program prior to sending the variable to Python was exactly what I needed, and my Python script ran as expected.

Regular expressions have been a great addition to my toolbox, and I’ve been using them more and more in my programs. In the next part of this series, I’ll show some other regular expression functions that are available to us on IBM i.

Mike Larsen is a Director of Information Technology at Auburn Pharmaceutical and an associate of Central Park Data Systems and has been working with IBM i systems for over 20 years.  He specializes in RPG, CL, and SQL and recently has been working with PHP and Python.  Current projects have given Mike the opportunity to work with generating and parsing XML and JSON from SQL and consuming SOAP and REST web services.  Although his main area of expertise is on IBM i, Mike has a passion for learning other languages and how he can integrate other platforms with IBM i.