Evaluate if a value is numeric

[lynchg]

I have been searching for a key word or a function to evaluate if a value in a field is numeric. I am familiar with other environments where you can use:

IsNumeric(myValue) or myValue Is Numeric

There must be a way of making this evaluation, but I have searched in on-line manuals using every word I think of that might take me to an explanation and have found nothing so far.

I am using a DB2 7.1 client on an OS/390 mainframe.

 

[MarcLodge]

What field type are you trying this on, presumably CHAR?

 

[blom0344]

Does OS/390 support CASE statements?

For a CHAR(20) data-type expression like:

CASE WHEN
SUBSTR(FIELD,1,1) IN ('1','2',...........,'9','.',',') AND
SUBSTR(FIELD,2,1) IN ('1','2',...........,'9','.',',') AND
SUBSTR(FIELD,3,1) IN ('1','2',...........,'9','.',',') AND
.............
.............
SUBSTR(FIELD,20,1) IN ('1','2',...........,'9','.',',')
THEN 'Numerical'
ELSE 'Non-Numerical' END AS DATATYPE-CHECK

just a wild idea ........


T. Blom
Information analyst
tbl@shimano-eu.com

 

[lynchg]

Thank you for your replies, sorry that I left out the fact that this is a char field. OS/390 does support the Case statement and I used the same idea that blom0344 suggested. I threw that in just as a temporary solution thinking that there must be a function that does this evaluation. Do either of you know of any function to handle this?

It seems inconceivable that there wouldn't be. Silly me for wanted to perform such a bizarre operation, but don't get me started on DB2. Someone tell me again what a great product this is!

Sorry for the sarcasm, as you can tell I am not a fan.

 

[blom0344]

Sorry you are not very enchanted by DB2 (for OS/390).
I am using DB2 UDB / OS/400 and OS/390 may be the weakest in this department. Even if you can't have it of the shelf, there should be possibilities to roll your own UDF I suspect?
At least this is a good option for the other DB2 flavors....


T. Blom
Information analyst
tbl@shimano-eu.com