Numeric check

prem18 · #1 (**permalink**) 10-31-08, 17:40

Is there any way to validate numeric characters in db2?

Peter.Vanroose · #2 (**permalink**) 11-01-08, 10:57

Quote:

Originally Posted by prem18

Is there any way to validate numeric characters in db2?

I would CAST() them to a numeric type (DECFLOAT or DECIMAL or INT) and inspect the potential error message.

Peter.Vanroose · #3 (**permalink**) 11-01-08, 11:10

E.g., on DB2 v8 for z/OS, the following casts return:

Code:

cast('123.45' AS dec(10,3))              SQLCODE=   0
cast('123.45' AS dec(5,3))               SQLCODE=-420
cast('123.45' AS int)                    SQLCODE=-420
cast('invalid' AS int)                   SQLCODE=-420

stolze · #4 (**permalink**) 11-01-08, 13:51

On DB2 LUW, you can put the cast into a stored procedure, add an exception handler for the respective SQLSTATE and then call the stored proc from a UDF so that you can use the function in your SQL statements. Search for "Serge Rielau" and "soft_int" in your favorite search engine.

n_i · #5 (**permalink**) 11-01-08, 20:39

Seems to be a FAQ:

Query on integer values in a mixed table

Peter.Vanroose · #6 (**permalink**) 11-02-08, 02:16

Especially note the possibilities of the DECFLOAT datatype:
it allows for numeric expressions like e.g. "123e-7" or even "+Inf" or "NaN".

Code:

CAST('1.2345e56' AS DECFLOAT)    --> SQLCODE = 0
CAST('abcdef' AS DECFLOAT)       --> SQLCODE = -420

prem18 · #7 (**permalink**) 11-03-08, 15:08

Thanks for everyone. I handled it by capturing the sql code -420 as mentioned above.

Internet Services

Web Development

Digital Marketing

Consumer Tech