What about the following function:
CREATE or Replace FUNCTION HSRRRPG/TESTN (
PARText VarChar(60) DEFAULT '0')
RETURNS Char(1)
LANGUAGE SQL
SPECIFIC HSRRRPG/TESTN
DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
SET OPTION DBGVIEW = *SOURCE
BEGIN
Declare LocNum Dec(31, 9) Default 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION RETURN '0' ;
Set LocNum = Dec(Replace(ParText, ' ', ''), 31, 9);
Return '1';
END ;
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Hiebert,
Chris
Sent: Donnerstag, 22. Dezember 2016 17:31
To: rpg400-l@xxxxxxxxxxxx
Subject: RE: SQL function equivalent to TestN?
We are on V7R1 and can use regular expressions to replicate that behavior.
Here is a very simple example:
WITH T1 (VFLD) AS (VALUES ('not a number'),('156,156'),('1 '),(' 1 ') )
/* in order to run INT your string can't have any commas */ select
INT(replace( VFLD , ',' , '' )) Where length(trim(vfld)) > 0 And
/* skip strings that have anything that is not a digit or a comma */
REGEXP_COUNT( trim(vfld) , '[^(\d|,)]' ) = 0 and
/* Include strings that have a digit or a comma. */ REGEXP_COUNT( trim(vfld)
, '[(\d|,)]' ) > 0 ;
Chris Hiebert
Senior Programmer/Analyst
Disclaimer: Any views or opinions presented are solely those of the author
and do not necessarily represent those of the company.
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Koester,
Michael
Sent: Thursday, December 22, 2016 8:47 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: SQL function equivalent to TestN?
Is there anything in SQL that can test a string for a valid numeric?
I am looking to extract the minimum value from a character column in
selected rows, but I only want to consider those where the column has a
numeric value.
Simplified, I'm looking for something like:
Exec sql
select min(columnOfwhoKnowsWhat)
from table1
where [columnOfwhoKnowsWhat (a 10-character field) only contains some whole
number between 1 and 99,999 (possibly with a comma) ];
The column I need to test is a number of feet from a location, but being a
"miscellaneous" field, may instead have all manner of other data instead,
including values with more than one decimal point, e.g. ".057 .034 .032"
(yeah, there's even an embedded space).
I know I can declare a cursor, fetch each row and test with RPG, but if SQL
can do it, that would be preferable.
Had to ask...
Thanks.
Michael Koester
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (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.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (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.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD
midrange.com
