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
midrange.com
