sql server - 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

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