What's wrong with this simple SQL UDF?

[Larry Menard]

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.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"

[Chris]

Larry,

Actually, the UDF is failing when the outer IF is commented out - as
how you wrote it above. The problem is that it is failing on the very
first character then, and returning 1 as the location of the mismatch,
which unfortunately matches your success condition.

If you chance the success flag to be -1, you will see this.

The reason that 12 is returning when you don't comment out the outer IF
is that the twelfth position, which doesn't match, happens to have a %
in the pattern at the 11th position, so the code decides that that is
fine, and proceeds on to the 12th position, where the space doesn't
match the 'r'.

-Chris

[Larry Menard]

It always seems so simple when someone else explains it. ;-)

Thanks, Chris.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Chris" <cc******@msn.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

Larry,

Actually, the UDF is failing when the outer IF is commented out - as
how you wrote it above. The problem is that it is failing on the very
first character then, and returning 1 as the location of the mismatch,
which unfortunately matches your success condition.

If you chance the success flag to be -1, you will see this.

The reason that 12 is returning when you don't comment out the outer IF
is that the twelfth position, which doesn't match, happens to have a %
in the pattern at the 11th position, so the code decides that that is
fine, and proceeds on to the 12th position, where the space doesn't
match the 'r'.

-Chris