Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2006
    Location
    Columbus, OH
    Posts
    69

    Unanswered: Query on integer values in a mixed table

    AIX 5L
    DB2 8.2

    Here is the problem I have.


    I have a table with a column that contains strings and numbers.

    Ex:

    90
    85
    lt30
    EMPTY
    15
    10

    I want to pull back records that are greater than 80
    select * from tableA where INT(col1) > 80

    but as soon as the query reaches a string that can't be converted to an Integer it bombs out

    SQL0420N Invalid character found in a character string argument of the
    function "INTEGER". SQLSTATE=22018

    Is there a way through the WHERE clause, INT conversion, CASE stmt, I can ignore the string as another record that evaluates false, so the query completes with a result set of

    90
    85


    Thanks in advance,

    Charlie

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Code:
    ...where translate(col1,'','0123456789') = ''
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Sep 2006
    Location
    Columbus, OH
    Posts
    69
    Still not sure how this works, but it works beautifully. Thanks.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    It replaces every digit in the string with a blank. If what's left is a blank string, it means there was nothing but digits, therefore it can be cast to an integer without errors.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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