• 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
    UCG Technologies

    CYBER-ATTACKS ON THE RISE. PROTECT WITH THE TRIPLE PLAY.

    COVID-19 has not only caused a global pandemic, but has sparked a “cyber pandemic” as well.

    “Cybersecurity experts predict that in 2021, there will be a cyber-attack incident every 11 seconds. This is nearly twice what it was in 2019 (every 19 seconds), and four times the rate five years ago (every 40 seconds in 2016). It is expected that cybercrime will cost the global economy $6.1 trillion annually, making it the third-largest economy in the world, right behind those of the United States and China.”1

    Protecting an organization’s data is not a single-faceted approach, and companies need to do everything they can to both proactively prevent an attempted attack and reactively respond to a successful attack.

    UCG Technologies’ VAULT400 subscription defends IBM i and Intel systems against cyber-attacks through comprehensive protection with the Triple Play Protection – Cloud Backup, DRaaS, & Enterprise Cybersecurity Training.

    Cyber-attacks become more sophisticated every day. The dramatic rise of the remote workforce has accelerated this trend as cyber criminals aggressively target company employees with online social engineering attacks. It is crucial that employees have proper training on what NOT to click on. Cyber threats and social engineering are constantly evolving and UCG’s Enterprise Cybersecurity Training (powered by KnowBe4) is designed to educate employees on the current cutting-edge cyber-attacks and how to reduce and eliminate them.

    A company is only as strong as its weakest link and prevention is just part of the story. Organizations need to have a quick response and actionable plan to implement should their data become compromised. This is the role of cloud backup and disaster-recovery-as-a-service (DRaaS).

    Data is a company’s most valuable asset. UCG’s VAULT400 Cloud Backup provides 256-bit encrypted backups to two (2) remote locations for safe retrieval should a cyber-attack occur. This is a necessary component of any protection strategy. Whether a single click on a malicious link brings down the Windows environment or an infected SQL server feeds the IBM i, once the data is compromised, there is no going back unless you have your data readily available.

    Recovery is not a trivial task, especially when you factor in the time sensitive nature of restoring from an active attack. This leads to the third play of the Triple Play Protection – DRaaS.  Companies have myriad concerns once an attack is realized and a managed service disaster recovery allows employees to keep focus on running the business in a crisis state.

    The combination of training employees with secure backup and disaster recovery offers companies the best chance at avoiding financial disruption in an age of stronger, more frequent cyber-attacks.

    Reach out to UCG Technologies to discuss your company’s security needs and develop a data protection plan that fits you best.

    ucgtechnologies.com/triple-play

     800.211.8798 | info@ucgtechnologies.com

     

    1. https://theconversation.com/cyberattacks-are-on-the-rise-amid-work-from-home-how-to-protect-your-business-151268

    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

  • Doing The Texas Two Step From Power9 To Power10
  • PHP’s Legacy Problem
  • Guru: For IBM i Newcomers, An Access Client Solutions Primer
  • IBM i 7.1 Extended Out To 2024 And Up To The IBM Cloud
  • Some Practical Advice On That HMC-Power9 Impedance Mismatch
  • IBM Extends Dynamic Capacity Pricing Scheme To Its Cloud
  • Here’s What You Should Do About The IBM i Skills Shortage
  • Matillion Founder Recounts Midrange Roots
  • Four Hundred Monitor, February 24
  • IBM i PTF Guide, Volume 23, Number 8

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 © 2021 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.