• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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.

    RELATED STORIES

    Native Regular Expressions In DB2 For i 7.1 And 7.2

    Fuzzy Matching in RPG

    LOCATE

    REGEXP_INSTR

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: 400guru, Four Hundred Guru, Guru, IBM i, RPG, SQL, System/36

    Sponsored by
    OCEAN User Group

    OCEAN TechCon22 – July 21-23, 2022
    Three Days of Inspiration & Innovation!
    In-Person & Online

    Join your IBM i Community for hands-on learning from technical presentations and workshops, plus a Vendor Solutions Expo. Technically, We’re passionate about continuing education on the IBM Power Systems platform!

    Register Now!

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Four Hundred Monitor, June 24 ARCAD Brings Traditional 5250 Development Into DevOps Fold

    One thought on “Guru: When %SCAN Isn’t Sufficient”

    • Ken says:
      June 24, 2019 at 9:32 am

      Hello Ted,

      Thanks for the tip. I did not know you could use like to check for multiple values. That is really cool.

      Ken

      Reply

    Leave a Reply Cancel reply

TFH Volume: 29 Issue: 39

This Issue Sponsored By

  • Maxava
  • ProData Computer Services
  • RPG & DB2 Summit
  • iTech Solutions
  • T.L. Ashford

Table of Contents

  • How Big Blue Stacks Up IBM i On Premises And On Cloud
  • ARCAD Brings Traditional 5250 Development Into DevOps Fold
  • Guru: When %SCAN Isn’t Sufficient
  • Four Hundred Monitor, June 24
  • IBM i PTF Guide, Volume 21, Number 25

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Guild Mortgage Takes The 20-Year Option For Modernization
  • IBM i Licensing, Part 3: Can The Hardware Bundle Be Cheaper Than A Smartphone?
  • Guru: The Finer Points of Exit Points
  • Big Blue Tweaks IBM i Pricing Ahead Of Subscription Model
  • We Still Want IBM i On The Impending Power E1050
  • DRV Brings More Automation to IBM i Message Monitoring
  • Managed Cloud Saves Money By Cutting System And People Overprovisioning
  • Multiple Security Vulnerabilities Patched on IBM i
  • Four Hundred Monitor, June 22
  • IBM i PTF Guide, Volume 24, Number 25

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.