• 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
    WorksRight Software

    Do you need area code information?
    Do you need ZIP Code information?
    Do you need ZIP+4 information?
    Do you need city name information?
    Do you need county information?
    Do you need a nearest dealer locator system?

    We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

    The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

    PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

    Just call us and we’ll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4.

    WorksRight Software, Inc.
    Phone: 601-856-8337
    Fax: 601-856-9432
    Email: software@worksright.com
    Website: www.worksright.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

  • IBM i Delivers Sizable Benefits, Forrester Consulting Reports
  • SBOMs Will Come to IBM i, Eventually
  • IBM i Backup Provider Storagepipe Snapped Up By Thrive
  • Four Hundred Monitor, June 7
  • IBM i PTF Guide, Volume 25, Number 23
  • Power10 Boosts NVM-Express Flash Performance
  • Fortra Completes Postmortem Of GoAnywhere Vulnerability
  • Guru: Binding Directory Entries
  • How Does Your Infrastructure Spending Stack Up To The World?
  • IBM i PTF Guide, Volume 25, Number 22

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 © 2023 IT Jungle