RE: SQL function equivalent to TestN?

["Koester, Michael" <mkoester@xxxxxxxxxxxxx>]

I like it!
Thanks Chris.

Michael Koester

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