RE: SQL function equivalent to TestN?

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
Hiebert, Chris
Sent: Thursday, December 22, 2016 11:31 AM
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