MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » September 2012

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



fixed

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.
++
Dennis
++
"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
string
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
source.



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



SELECT TEXT_COLUMN

FROM THAT_TABLE

WHERE REGEXP_LIKE(TEXT_COLUMN, '[0-9]{6}') = '1'







Enjoy.





Dennis Lovelady

http://www.linkedin.com/in/dennislovelady




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
list
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.





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

This mailing list archive is Copyright 1997-2014 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