MIDRANGE dot COM Mailing List Archive

Re: SQL UDF for Regular Expression usage in a WHERE clause


More info:

FINAL CALL Specifies that a final call is made to the function. To
differentiate between final calls and other calls, the function
receives an additional argument that specifies the type of call.


The types of calls are:
First Call
Specifies the first call to the function for this reference to the
function in this SQL statement. A first call is a normal call. SQL
arguments are passed and the function is expected to return a result.

Normal Call
Specifies that SQL arguments are passed and the function is
expected to return a result.

Final Call
Specifies the last call to the function to enable the function to
free resources. A final call is not a normal call. If an error occurs,
the database manager attempts to make the final call. A final call
occurs at these times:
v End of statement: When the cursor is closed for cursor-oriented
statements, or the execution of the statement has completed.
v End of a parallel task: When the function is executed by parallel tasks.
v End of transaction: When normal end of statement processing does not
occur. For example, the logic of an application, for some reason,
bypasses closing the cursor.


On Wed, Sep 19, 2012 at 10:01 AM, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:

You stole my idea! :)

One improvement I might suggest, would be to make use of the FINAL
CALL clause on the CREATE FUNCTION and it's corresponding parameters:
- An INTEGER input parameter for the call type, if FINAL CALL was
specified on the CREATE FUNCTION statement.

This would allow you to ensure that the regex is only compiled once
and to make sure you clean up after the last call.


On Wed, Sep 19, 2012 at 7:08 AM, Dennis <iseries@xxxxxxxxxxxx> wrote:
Folks, I just noticed that the SQL templates in my example for creating both functions, were slightly incorrect.

Both had PARAMETER STYLE GENERAL, and they both should have been PARAMETER STYLE SQL.

I apologize for this oversight. That's what happens when I copy comments from one module to a new copy.
"The defect of equality is that we only desire it with our superiors."
-- Henry Becque

Bravely sent from my Galaxy tablet phone.

Dennis Lovelady <iseries@xxxxxxxxxxxx> wrote:

Hello, RPG folks.

I followed a discussion on midrange-l where someone wanted to find a
of digits within a table column using SQL. The discussion led me to
write a
UDF for i that emulates as much as possible the regexp_like function
available in Oracle. To that end, I have written and am now pleased to
share that via http://code.midrange.com/907466d593.html

Note that Scott Klement gets credit for the Regular Expressions header
(REGEX_H) that I got from one of his functions, eons ago.

I decided it would be good to share it on this list as well.

Simple compile hint and template for function creation are in the

Using that, if you wanted to find a string of at least 6 consecutive
(for example), you might do something like:





Dennis Lovelady


I have never hated a man enough to give his diamonds back.
- Zsa Zsa Gabor

This is the RPG programming on the IBM i / System i (RPG400-L) mailing
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2015 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact