Results 1 to 7 of 7

Thread: Numeric check

  1. #1
    Join Date
    Apr 2007
    Posts
    63

    Unanswered: Numeric check

    Is there any way to validate numeric characters in db2?

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Apr 2007
    Posts
    63

    Smile

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •