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

  • I suspect it may be a space within the number, for example this will cause the error

    SELECT CAST('123 456' AS BIGINT)

    If you want to compress this situation into a single number then you could do

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

    THEN Cast(REPLACE(h.oe_po_no,' ','') as BigInt)

    ELSE 0

    END = 0

    Otherwise to throw a zero

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

    THEN Cast(RTRIM(LTRIM(h.oe_po_no)) as BigInt)

    ELSE 0

    END = 0