none
SQL skip cast error

    Question

  • Hello and thank you in advance,

    I have a query like:

    select * from table1 a, table 2 b

    where CAST(a.DATA AS bigint) = CAST(b.DATA AS bigint)

    but I got some rows on a and b that have text values that cannot be transform to numeric.

    I need to skip those errors rows on cast.


    Gilberto H.

    Friday, March 15, 2013 5:30 PM

Answers

All replies

  • Only convert those values where ISNUMERIC() = 1

    WHERE ISNUMERIC(a.DATA) = 1
          AND ISNUMERIC(b.DATA) = 1
          AND CAST(a.DATA AS BIGINT) = CAST(b.DATA AS BIGINT)


    David Dye My Blog

    Friday, March 15, 2013 5:47 PM
    Moderator
  • Hi Gilberto,

    You can use INNER JOIN here, in that case you need to check if any one of column is numeric.

    SELECT	A.*, B.*
    FROM	Table1 A
    INNER JOIN Table2 B ON A.Data = B.Data
    WHERE ISNUMERIC(A.Data) = 1
    --WHERE ISNUMERIC(B.Data) = 1


    - Vishal

    SqlAndMe.com

    Saturday, March 16, 2013 3:55 AM
  • Thank you everybody, I was able to do it with isnumeric,

    I also have to add that since I want to convert to int, I manually need to take out items with decimals  using.

    and not like '%,%'

    and not like '%.%'


    Gilberto H.

    Tuesday, March 19, 2013 3:05 PM