• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Fuzzy Matching In RPG

    December 3, 2014 Ted Holt

    Note: The code accompanying this article is available for download here.

    SQL allows you to use wildcard characters with the LIKE operator to search a column for a pattern. As they say in the GEICO commercials, “Everybody knows that.” Well, did you know you can do the same thing in RPG programs?

    If you’re not familiar with LIKE, read about it here. You can use the LIKE operator to find data with inexact matches. For instance, find all customers whose names contain “ACME”.

    select * from cust
     where name like '%ACME%'
    
       ID  NAME
    =====  ==========================================
    19883  ACME Amalgamated Widget and Doodad Company
    58842  ACME Glass Home Builders, LLC
    

    If you need a case-insensitive search, use the UPPER (or UCASE) function.

    select * from cust
     where upper(name) like '%ACME%'
    
       ID  NAME
    =====  ==========================================
    19883  ACME Amalgamated Widget and Doodad Company
    39003  Pete Slacmen Tricycle Supplies, Inc.
    58842  ACME Glass Home Builders, LLC
    

    Another thing everybody knows is that we can use SQL features in RPG programs. Subprocedure MATCHES uses LIKE with RPG host variables.

    dcl-proc  Matches;
    
       dcl-pi Matches  ind;
          inSource     like(String)  const  options(*trim);
          inPattern    like(String)  const  options(*trim);
       end-pi;
    
       dcl-s  Matched  char(1);
    
       // Try for an exact match
       if inSource = inPattern;
          return *on;
       endif;
    
       // Try for a wild-card match
       exec sql
          set :Matched = case
             when :inSource like :inPattern
                then '1' else '0' end;
       if Matched = '1';
          return *on;
       endif;
    
       // Otherwise, no match.
       return *off;
    
    end-proc Matches;
    

    Here it is in partial fixed-format code.

    P Matches         b
    
    D Matches         pi              n
    D   inSource                          like(String) const options(*trim)
    D   inPattern                         like(String) const options(*trim)
    
    D Matched         s              1a
    
     /free
         // Try for an exact match
         if inSource = inPattern;
            return *on;
         endif;
    
         // Try for a wild-card match
         exec sql
            set :Matched = case
               when :inSource like :inPattern
                  then '1' else '0' end;
         if Matched = '1';
            return *on;
         endif;
    
         // Otherwise, no match.
         return *off;
     /end-free
    P Matches         e
    

    MATCHES first attempts an exact match of two strings. If that doesn’t work, it tries the LIKE operator. The following RPG source code assigns a value of *ON to indicator FOUND.

    SearchName = 'ACME%Widget%Corporation';
    CustomerName = 'ACME Amalgamated Widget and Doodad Corporation';
    
    Found = Matches(CustomerName: SearchName);
    

    I forget where I learned this technique, but I think it was from something Birgitta Hauser posted on the Web. (Birgitta’s truly a guru, but everybody knows that.) To whoever it was, I say, “many thanks!” I have production code that runs all day long and uses this technique.

    RELATED REFERENCE

    LIKE Predicate, IBM i 7.1 Knowledge Center



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    LaserVault

    Integrate Virtual Tape For Better Backups, Faster Recovery, And More Flexibility

    Virtual tape and virtual tape libraries offer a way to both simplify and strengthen backup and recovery operations. By incorporating virtual tape technology, automation of backups becomes possible resulting in hundreds of hours saved annually for IT departments and personnel.

    LaserVault ViTL is a virtual tape and tape library solution developed specifically for use with IBM Power Systems (from AS/400 to iSeries to Power 9s). See a demo and get a $50 gift card.

    With ViTL you can:

    • Replace physical tape and tape libraries and associated delays
    • Automate backup operations, including the ability to purge or archive backups
    • Remotely manage your backups – no need to be onsite with your server
    • Save backups to a dedupe appliance and the cloud
    • Recover your data at lightspeed greatly improving your ability to recover from cyberattacks
    • And so much more

    “The ViTL tapeless solution has truly made my job easier. It has given me more confidence in our full system recovery ability – but at the same time I hope it is never needed.” IBM i Administrator at a financial services company

    Sign-up now to see a ViTL online demo and get a $50 Amazon e-gift card when the demo is complete as our way of saying thanks for your time. Plus when you sign-up you’ll receive a free facts comparison sheet on using virtual tape vs tape so you can compare the functionality for yourself.

    LaserVault.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Cilasoft:  Stay on top of your most difficult IBM i security challenges with our Auditing and Security Suite.
    Profound Logic Software:  Now On-Demand Webinar: "See What i Can Do with Mobile Applications"
    BCD:  Beat Your Q4 Numbers with Real-Time Analytics on IBM i. Watch video!

    More IT Jungle Resources:

    System i PTF Guide: Weekly PTF Updates
    IBM i Events Calendar: National Conferences, Local Events, and Webinars
    Breaking News: News Hot Off The Press
    TPM @ EnterpriseTech: High Performance Computing Industry News From ITJ EIC Timothy Prickett Morgan

    Web Dev Tool from mrc Goes ‘Responsive’ Entry Power8 Systems Get Express Pricing, Fat Memory

    Leave a Reply Cancel reply

Volume 14, Number 26 -- December 3, 2014
THIS ISSUE SPONSORED BY:

Focal Point Solutions Group
WorksRight Software
Shield Advanced Solutions

Table of Contents

  • Fuzzy Matching In RPG
  • TR8 DB2 For i Enhancements, Part 2
  • Admin Alert: What Should An IBM i Administrator Do, Part 1

Content archive

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

Recent Posts

  • COMMON Set for First Annual Conference in Three Years
  • API Operations Management for Safe, Powerful, and High Performance APIs
  • What’s New in IBM i Services and Networking
  • Four Hundred Monitor, May 18
  • IBM i PTF Guide, Volume 24, Number 20
  • IBM i 7.3 TR12: The Non-TR Tech Refresh
  • IBM i Integration Elevates Operational Query and Analytics
  • Simplified IBM i Stack Bundling Ahead Of Subscription Pricing
  • More Price Hikes From IBM, Now For High End Storage
  • Big Blue Readies Power10 And IBM i 7.5 Training for Partners

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.