Replacing parts of strings using regexp

The fifth installment of my examples of the using regular expressions added to Db2 for i as part of IBM i 7.3 TR2 and 7.2 TR6 is going to be about REGEXP_REPLACE. I can already replace parts of strings using SQL's REPLACE and RPG's %SCANRPL, but REGEXP_REPLACE allows me to use regular expressions to find the pattern I wish to replace.

The syntax for the REGEXP_REPLACE is:

REGEXP_REPLACE(source-string, search-pattern, replacement-string, start-position, occurrence, flags)

1. source-string What is searched, it can be a string or variable.
2. search-pattern What I am looking for in the search string.
3. replacement-string What I want to replace the search pattern with.
4. start-position Where in the source string I want to start the search for the search pattern.
5. occurrence Do I want the first, second, third, etc. occurrence of the search pattern in the search string.
6. flags These are the regular expression flags, the same ones that are used in the other Db2 for i regular expressions.

In the following examples I am going to work with the same source string in RPG program. Let me start with defining the variables I will be using.

01 dcl-s S1 char(60) ; 02 dcl-s S2 like(S1) ; 03 S1 = 'RPGPGM.COM is the best website for rpg examples' ;

Line 1: This is the variable that will contain the string I will be using. I would normally use the INZ to initialize this variable with the string. But due to width of this example I am moving the value into the string on line 3.

Line 2: This variable will be used to contain the result of the REGEXP_REPLACE.

When working with a string like this in a program I prefer not to use a SQL SELECT statement with a dummy file. I prefer to use SET, as I will be doing in these examples. I will give an example at the end of this post showing how to do the same with in a SELECT.

And now onto the first example:

04 exec sql SET :S2 = REGEXP_REPLACE(:S1,'rpg','RPG',1,1) ;

This string will replace the first occurrence of lower case rpg with the upper case RPG.

'RPGPGM.COM is the best website for RPG examples '

If I wanted to change regardless of case, I would use the regular expression i. I mention more about these flags in my first post about the regular expression REGEXP_LIKE. I also need to say change the second occurrence of "RPG", otherwise it would change the one at the start of the search string.

05 exec sql SET :S2 = REGEXP_REPLACE(:S1,'rpg','RPG',1,2,'i') ;

This gives me the same result as the previous example.

If I use c flag only when the letters' case in the search pattern matches will the replace occur. Therefore, I can search for the first occurrence of rpg in the string.

06 exec sql SET :S2 = REGEXP_REPLACE(:S1,'rpg','RPG',1,1,'c') ;

The result is the same as the other two examples.

As I write about more than RPG on this blog let me change string to reflect that.

07 exec sql SET :S2 = REGEXP_REPLACE(:S1,'RPG','CL, SQL, & RPG', 1,2,'i') ;

Here I am replacing the second occurrence of RPG with CL, SQL, & RPG. As the i is present this will find the second occurrence regardless of case.

'RPGPGM.COM is the best website for CL, SQL, & RPG examples '

What if I want to change all occurrences of a search pattern. In this example I want to replace every space with a underscore ( _ ). To stipulate all occurrences of a search pattern the value zero ( 0 ) is given.

08 exec sql SET :S2 = REGEXP_REPLACE(:S1,' ','_',1,0,'i') ;

My result is:

'RPGPGM.COM_is_the_best_website_for_rpg_examples_____________'

This is not really what I want. I do not want any underscores after the end of "examples". I can modify my expression and use RTRIM to trim the spaces from the end of string.

09 exec sql SET :S2 = REGEXP_REPLACE(RTRIM(:S1),' ','_',1,0,'i') ;

By right trimming the string in the variable S1 on the spaces in that part of the string are changed.

'RPGPGM.COM_is_the_best_website_for_rpg_examples '

I can also replace part of the string with null, yes, that is replace part of the string with nothing.

10 exec sql SET :S2 = REGEXP_REPLACE(:S1,'rpg','',1,1) ;

By having the apostrophes next to each other indicates null. Therefore, in this example I want to remove the first occurrence of rpg.

'RPGPGM.COM is the best website for examples '

As I removed the rpg there is now two spaces between the "for" and "examples". If I change my statement to include a space in the search pattern I can remove one of the spaces too.

11 exec sql SET :S2 = REGEXP_REPLACE(:S1,'rpg ','',1,1) ;

Now there is only one space, where there was two before.

'RPGPGM.COM is the best website for examples '

As promised here is a Select statement example:

12 exec sql SELECT REGEXP_REPLACE(S1,'rpg','the best',1,1,'c') INTO :S2 FROM SYSIBM.SYSDUMMY1 ;

In this example I want to replace the first occurrence of lower case rpg with the best. The result is put into the variable S2. I could use any table or file on my system in the FROM clause, but I use IBM's dummy table to help others understand that S1 is not coming from a table. My result is.

'RPGPGM.COM is the best website for the best examples '

 

Other Db2 for i regular expressions:

• REGEXP_LIKE
• REGEXP_COUNT
• REGEXP_INSTR
• REGEXP_SUBSTR

 

You can learn more about the REGEXP_REPLACE from the IBM website here.

 

This article was written for IBM i 7.3 TR2 and 7.2 TR6.