SQL Server 2005 IsNumeric Doesn't Catch '0310D45'

Question

I have this value "0310D45"

I use isnumeric to check if the values ​​are numeric before casting in bigint. Unfortunately, this value skips the isnumeric check. Therefore, my request fails:

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

What is the easiest way to handle this. I was thinking about using charindex, but I would need to check all 26 letters.

Is there a simple solution that I don't see? I really don't want to create a custom function.

thanks

Answer 1

Take a look at this article titled What's Wrong with IsNumeric ()? which contains the following thesis:

Summary: The T-SQL ISNUMERIC () function has a problem. This can falsely interpret non-numeric letters and characters (such as D, E and E) and even tabs (CHAR (9)) as numeric.

Unfortunately, IsNumeric seems to be just weird, and you have to write a few T-SQL lines to get around it. (By strange, I mean that IF the received data can be converted to ANY numeric type in general, it will be converted.)

Answer 2

I recently ran into this problem and was looking for a solution. I think I found two, and wanted to publish them here so that it would be easier for others to find them. The first solution is to use the regular expression and SQLServer PATINDEX () function

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

The second solution is to combine the string 'e0' into a test string and use the SQLServer ISNUMERIC () function with the concatenated string. ISNUMERIC does not detect the presence of characters, such as d, e, x, due to the different notations used in number formats, but it still allows only one character to be used. Thus, the concatenation of "e0" does not allow the function to give you false truth when it is required.

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

Hope this helps

Answer 3

Have a look at this SO question for a few alternative SQL Server ISNUMERIC () clauses.

I believe Erland has this as a connection element in his wish list - what he calls is_valid_convert ().