Fuzzy Matching In RPG

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