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 named What is wrong with IsNumeric()? which contains the following abstract:

Abstract: T-SQL's ISNUMERIC() function has a problem. It can falsely interpret non-numeric letters and symbols (such as D, E, and £), and even tabs (CHAR(9)) as numeric.

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. (By weird I mean that IF the data evaluated can be converted into ANY numeric type at all, the it will get converted.)

Answer 2

I recently faced this problem, and was looking for solution. I think I found two, and wanted to post them here so that its easier for others to find. First solution is to use regular expression and SQLServer function PATINDEX()

IF PATINDEX('%[^0-9]%', @testString) = 0

Second solution is to concatenate a string 'e0' to your test string and still use SQLServer function ISNUMERIC() with the concatenated string. ISNUMERIC fails to detect presence of characters such as d, e, x because of different notations used in the numeric formats, but it still allows only a single character. Thus concatenating 'e0' prevents the function from giving you a false true, when ever required.

IF (ISNUMERIC (@testString + 'e0') = 1)

Hope this helps

Answer 3

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().