Hi Rob,
Thanks for the response. You're right, '123.45' would not get picked up as numeric. I like Birgitta's response about using %dec with a monitor around it.
To my taste, I prefer to only use the %dec test when the incoming text value has a period in it to avoid getting job log entries for values that are not numeric and don't contain periods. So I added in the following code after the %check.
// If not all numbers, but contains a period, then check to
// see if this is a number with a decimal.
ElseIf %scan( '.': checkText ) > 0;
Monitor;
number = %dec( checkText: 33: 9 );
rtnNumeric = *On;
On-Error;
rtnNumeric = *Off;
EndMon;
I'm glad my attempt to help resulted in others helping me as well.
-Kurt
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Tuesday, January 15, 2013 11:54 AM
To: Midrange Systems Technical Discussion
Subject: RE: SQL is numeric function
Gives an example of how to extend your rpg subprocedure to become a UDF and then be used by SQL but the rpg may be a bit simplistic. For example,
123.45 would not be numeric. Just adding a period to the check string may not be sufficient for the example another poster gave: 123.156.121.45 or some such thing. That's 'numeric' enough for an IP address but not numeric for mathematical functions.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From: "Anderson, Kurt" <KAnderson@xxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>,
Date: 01/15/2013 12:47 PM
Subject: RE: SQL is numeric function
Sent by: midrange-l-bounces@xxxxxxxxxxxx
Hi Joel,
I actually created my own and don't mind sharing.
Here's the function. xxxLIB is the library you'll be placing it in.
Create or Replace Function xxxlib/isNumeric ( VALUE VarChar(25) )
returns Char(1)
language rpgle
no sql
specific ccdlib/isNumericV
external name 'xxxLIB/TEXTPROCS($ISNUMERIC)'
parameter style general
;
I have a second overloaded function that handles being passed a number.
Sounds obvious I suppose, but meh.
Create or Replace Function xxxlib/isNumeric ( VALUE dec(25,9) )
returns char(1)
language sql
deterministic
specific xxxlib/isNumericN
return '1'
;
Here's the procedure to check if the input is numeric.
As you see in the procedure above, I have it in a service program called
TEXTPROCS (text procedures service program). You can put it wherever.
//=======================================================================*
// Procedure: $isNumeric *
// Description: Is the Input Numeric? *
// *
// Notes: This procedure is also wrapped in a SQL User
Defined *
// Function located in CCDLIB/QSQL(ISNUMERIC) *
// *
// Parameters: *
// Input - Text to test *
// Inp/Out - n/a *
// Output - n/a *
// *
// Return Value: '1' - Numeric *
// '0' - Not Numeric *
// *
//-----------------------------------------------------------------------*
P $isNumeric B Export
D PI n
D epText 25a Const Varying
// Local Constants
D NUMBERS C '0123456789'
// Local variables
D checkText S Static Like( epText )
D rtnNumeric S n Static
//-----------------------------------------------------------------------*
/free
checkText = %trim( epText );
// If the length is zero, it's not numeric.
If %len( checkText ) = 0;
rtnNumeric = *Off;
// If only numbers were found, it is numeric.
ElseIf %check( NUMBERS: checkText ) = 0;
rtnNumeric = *On;
// Otherwise it's not numeric.
Else;
rtnNumeric = *Off;
EndIf;
Return rtnNumeric;
/end-free
P $isNumeric E
//-----------------------------------------------------------------------*
- Kurt Anderson
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [
mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Stone, Joel
Sent: Tuesday, January 15, 2013 10:51 AM
To: midrange-l@xxxxxxxxxxxx
Subject: SQL is numeric function
Does Iseries SQL have a numeric test?
For example:
Runsql 'select * from ItemMaster where itemNbr is not numeric'
______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs
Skyscan service.
For more information please visit
http://www.symanteccloud.com
______________________________________________________________________
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
