SQL Server 2005 IsNumeric Not catching '0310D45'

Question

I've got this value '0310D45'

I'm using isnumeric to check if values are numeric prior to casting to a bigint. Unfortunately this value is passing the isnumeric check. So my query is failing saying:

Msg 8114, Level 16, State 5, Line 3
Error converting data type varchar to bigint.

What is the simplest way to handle this. I was thinking of using charindex but I would have to check all 26 letters.

Is there a simple solution that I'm not seeing? I really don't want to create a user defined function.

Thanks

Answer 1

Take a look at this article. The guy already ran into, solved and blogged about your same issue.

Unfortunately it looks like IsNumeric is just plain weird and you will have to write a few lines of T-SQL to get around it, whether you use a UDF or not. Weird meaning that if the data evaluated can be converted into any numeric type at all, it will.

Answer 2

Have a look at this SO question for several alternative suggestions to the SQL Server ISNUMERIC().

I believe Erland has this as a connect item on his wishlist as well - something he calls is_valid_convert().