RE: SQL is numeric function

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.