CAST and IsNumeric

Question

Why would the following query return "Error converting data type varchar to bigint"? Doesn't IsNumeric make the CAST safe? I've tried every numeric datatype in the cast and get the same "Error converting..." error. I don't believe the size of the resulting number is a problem because overflow is a different error.

The interesting thing is, in management studio, the results actually show up in the results pane for a split second before the error comes back.

SELECT CAST(myVarcharColumn AS bigint)  
FROM myTable  
WHERE IsNumeric(myVarcharColumn) = 1 AND myVarcharColumn IS NOT NULL  
GROUP BY myVarcharColumn

Any thoughts?

Answer 1

I had the same issue in MSSQL 2014 triggered by a comma instead of full stop: isnumeric('9090,23') gives 1; cast('9090,23' as float) fails

I've replaced ',' with '.'

Answer 2

Try this and see if you still get an error...

SELECT CAST(CASE 
            WHEN IsNumeric(myVarcharColumn) = 0
                THEN 0
            ELSE myVarcharColumn
            END AS BIGINT)
FROM myTable
WHERE IsNumeric(myVarcharColumn) = 1
    AND myVarcharColumn IS NOT NULL
GROUP BY myVarcharColumn

Answer 3

Try wrapping it in a case:

select CASE WHEN IsNumeric(mycolumn) = 1 THEN CAST(mycolumn as bigint) END
FROM stack_table
WHERE IsNumeric(mycolumn) = 1
GROUP BY mycolumn

Answer 4

I came across this blog post that might help. I've not run into this issue before and not sure if it'll help you in this instance:

http://dotmad.blogspot.com/2007/02/cannot-call-methods-on-bigint-error.html

Answer 5

IsNumeric returns 1 if the varchar value can be converted to ANY number type. This includes int, bigint, decimal, numeric, real & float.

Scientific notation could be causing you a problem. For example:

Declare @Temp Table(Data VarChar(20))

Insert Into @Temp Values(NULL)
Insert Into @Temp Values('1')
Insert Into @Temp Values('1e4')
Insert Into @Temp Values('Not a number')

Select Cast(Data as bigint)
From   @Temp
Where  IsNumeric(Data) = 1 And Data Is Not NULL

There is a trick you can use with IsNumeric so that it returns 0 for numbers with scientific notation. You can apply a similar trick to prevent decimal values.

IsNumeric(YourColumn + 'e0')

IsNumeric(YourColumn + '.0e0')

Try it out.

SELECT CAST(myVarcharColumn AS bigint)
FROM myTable
WHERE IsNumeric(myVarcharColumn + '.0e0') = 1 AND myVarcharColumn IS NOT NULL
GROUP BY myVarcharColumn

Answer 6

I had the same Issue and I came up with the Scalar Function as Im on 2008 SQL

ALTER Function [dbo].[IsInteger](@Value VarChar(18))
Returns Bit
As 
Begin

  Return IsNull(
     (Select Case When CharIndex('.', @Value) > 0 
                  Then 0
                  Else 1
             End
      Where IsNumeric(@Value + 'e0') = 1), 0)    
End

If you are on 2012 you could use TRY_CONVERT