Published 20 November 2017
Avoid using ISNUMERIC() function
Avoid using the IsNumeric() function, because it can often lead to data type conversion errors, when importing data. If you’re working on SQL Server 2012 or later, it’s much better to use the Try_Convert()or Try_Cast() function instead. On earlier SQL Server versions, the only way to avoid it is by using LIKE expressions.
The stated purpose of IsNumeric() is to “determine whether an expression is a valid numeric type“. It accepts int, numeric, float and money numbers, and will tell you whether the string or number you provided can be cast or converted into a number. The trouble is that it won’t tell you what sort of number; it will tell you that something like ',' or '$-.' or '-' or '0E0' can be converted to a number but leaves you to guess what sort.
Learn more about this on the Redgate Hub: SQL Prompt Code Analysis: Avoid using the ISNUMERIC Function (E1029)