Has anyone else ever used the IsNumeric() function to try to eliminate this error (or similar):
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
yet still get the error? Isn’t that what the function is for? Here’s what is says in BOL:
ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0
But run this code (generates a list of ASCII Characters):
;WITH cteNumbers AS
SELECT TOP 255
ROW_NUMBER() OVER(ORDER BY NAME) AS n
sys.all_columns AS AC
n AS ASCII_code,
CHAR(n) AS [character],
ISNUMERIC(CHAR(n)) AS is_numeric
n <=255 AND
ISNUMERIC(CHAR(n)) = 1
And you’ll see that the first 10 rows and last 8 rows returned are not what I would consider numeric. Or how about this:
The first statement returns 1, while the second fails.
Here is the last interesting behavior of IsNumeric() in relation to Cast/Convert provided by Frank Kalis on this SQLServerCentral thread:
SELECT ISNUMERIC(0X0e) AS E, ISNUMERIC(0X0f) AS F
(1 row(s) affected)
SELECT CAST(0X0e AS INT) AS E, CAST(0X0f AS INT) AS F
The moral of the story is that IsNumeric() <> CanBeConvertedToNumeric().
So what is the answer? I don’t know. You would need to customize the solution to meet your specific situation. Jeff Moden suggests an IsAllDigits function in this thread on SQLServerCentral for instances where you want to eliminate rows with non-numeric characters.
Do you have anything that you recommend?