Guru: When %SCAN Isn’t Sufficient
June 24, 2019 Ted Holt
The RPG %SCAN built-in function is wonderful! I can still remember having to look for a string within a string using RPG II on System/36. What an ordeal that was! Yet in some situations %SCAN can’t do all I need it to do. In those cases, I rely on the power of SQL.
One case where SQL comes in handy is when I need a case-insensitive scan. Instead of RPG’s %SCAN function, I use SQL’s LOCATE and UPPER functions, like this:
dcl-s Description char(48);
dcl-s pos int (10);
exec sql
set :pos = locate ('HAMMER', upper(:Description));
If Description has the value Claw hammer 16oz, pos takes the value of 6.
Two similar SQL functions that you may want to read about are POSITION and POSSTR.
Another situation is with wild card scans. You can use SQL’s LIKE predicate for those.
dcl-s Description char(48);
dcl-s Matched char( 1);
exec sql
set :Matched = case
when :Description like '%16%oz%hammer%'
then '1' else '0' end;
This code determines whether Description refers to a 16-ounce hammer. Here are some values that cause Matched to be set to 1.
- 16oz claw hammer
- 16-oz. claw hammer
- 16 oz claw hammer
- Famousbrand 16 oz claw hammer
- 16 oz bricklayers hammer
You can also scan for regular expressions. Use the REGEXP_LIKE predicate.
dcl-s Description char(48);
dcl-s Matched char( 1);
exec sql
set :Matched = case
when regexp_like (:Description, '16.*oz.*(hammer|mallet)')
then '1' else '0' end;
This code determines whether Description refers to a 16-ounce hammer or mallet. Any of the values from the previous example cause Matched to be set to 1. Here are a couple more:
- 16oz rubber mallet
- Famousbrand 16 oz mallet
If you want the scan to ignore case, put an i in the third parameter of REGEXP_LIKE.
dcl-s Description char(48);
dcl-s Matched char( 1);
exec sql
set :Matched = case
when regexp_like (:Description, '16.*oz.*(hammer|mallet)', 'i')
then '1' else '0' end;
You can use the REGEXP_INSTR function instead of the REGEXP_LIKE predicate.
dcl-s Description char(48);
dcl-s pos int (10);
exec sql
set :pos =
regexp_instr (:Description, '16.*oz.*(hammer|mallet)');
If Description is Famousbrand 16oz claw hammer, pos is 13.
I really, really, really appreciate all IBM has done to give us wonderful functions like %SCAN in RPG. I also really, really, really appreciate their making SQL available to use in RPG programs. We’ve never had it so good.
Hello Ted,
Thanks for the tip. I did not know you could use like to check for multiple values. That is really cool.
Ken