Home Forums SQL Server 2008 T-SQL (SS2K8) Problems with IsNumeric and PatIndex when trying to convert string to Int/BigInt RE: Problems with IsNumeric and PatIndex when trying to convert string to Int/BigInt

  • April 11, 2013 at 8:39 pm

    #1605825

    tammyf (4/11/2013)

    Can someone help me understand why this is happening:

    If I put the following in a where clause:

    CASEWHEN PATINDEX('%[^0-9 ]%',h.oe_po_no) = 0

    THEN Cast(RTrim(LTrim(h.oe_po_no)) as BigInt)

    ELSE 0

    END = 0

    I get the following error:

    Msg 8114, Level 16, State 5, Line 1

    Error converting data type varchar to bigint.

    I have seen the same situation when using IsNumeric instead of PATINDEX. Why doesn't T-SQL truly recognize numeric strings and allow them to be converted? This is so frustrating.

    Thank you.

    Tammy

    The problem is the space in this: [^0-9 ]. It needs to be this: [^0-9].