Results 1 to 3 of 3

Thread: Is numeric???

  1. #1
    Join Date
    Jul 2003
    Los Angeles

    Unanswered: Is numeric???

    The vendor data we load in our staging table is rather dirty. One column in particular captures number data but 40% of the time has garbage characters or random strings.

    I have to create a report that filters out value ranges in that column. So, I tried playing with a combination of replace/translate like so

    select replace(translate(upper(str),' ','all possible char'),' ','')
    from table
    but it fails whenever it encounters a char I did not code. Therefore, the report can never be automated.

    Javascript has the isNaN() function to determine whether a value is an illegal number (True if it is and false if not).

    How can I do the same thing with DB2?? Do you have any idea?

    Thanks in advance.

  2. #2
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    Wouldn't it be easier to identify numbers instead of not-a-numbers? After all, there are only 10 possible decimal digits.

  3. #3
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    It is tougher to validate numbers than you might think. Consider -5.38e+6 which is a perfectly valid real number.

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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