Folks,

  I'm writing an SQL UDF to replace DB2's LIKE (which has some nasty limitations regarding concatenation), and I've reached a problem that I can't figure out.  It must be a coding or logic problem on my part, but I just can't see it.  Hopefully someone else can spot it.

  The following source is a bare-bones version of the UDF to illustrate the problem.

 connect to gallery2@
 create function g2_like (subject varchar(1024), pattern varchar(1024)) returns integer
 begin atomic

 declare subject_position, pattern_position integer;

 -- returns 1 if the pattern is matched
 -- returns the first failing position in the pattern if the pattern is not matched

 set subject_position = 1;
 set pattern_position = 1;

 while pattern_position <= length(pattern) do

 --  -- compare the two characters at the current positions
 --  if ((substr(pattern, pattern_position, 1) = '_') or
 --      (substr(pattern, pattern_position, 1) = substr(subject, subject_position, 1)))
 --  then -- Current two characters match (including '_' wildcard)
 --    -- nop, next iteration please;
 --  else -- Current two characters do not match

     if substr(pattern, pattern_position, 1) = '%'
     then -- but the pattern character is '%'
       -- removed for simplification
     else -- mismatch
       return pattern_position;
     end if;

   -- end if;

   set subject_position = subject_position + 1;
   set pattern_position = pattern_position + 1;

   end while;

 return 1;
 end@

 -- should match ('%' is in column 11 of the pattern)
 values g2_like ('The quick brown fox jumped over the lazy dog',
                 'The quick % fox jumped over the lazy dog')@
 connect reset@

  When I run it with the outer 'if' commented out, it runs fine, the pattern matches.

 values g2_like ('The quick brown fox jumped over the lazy dog', 'The quick % fox
  jumped over the lazy dog')

 1
 -----------
           1

   1 record(s) selected.

  But when I un-comment the outer "if", it no longer matches, and it says pattern_position is 12, where it should be 11.

 values g2_like ('The quick brown fox jumped over the lazy dog', 'The quick % fox
  jumped over the lazy dog')

 1
 -----------
          12

   1 record(s) selected.

  Can anyone offer any suggestions as to what's wrong?

  Thanks.