Check if a String is all Digits
SQL Server 2000: Tested
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Author: Jeff Moden
Date: 8 Dec 2010
A long standing bone of contention with many TSQL developers, especially those whose work encompasses any form of data import, is the wooliness of the ISNUMERIC check.
Its name would suggest that is will only return true if the string presented to it is a numeric. The crunch comes with what is considered to be numeric. A quick check of the MSDN definition of ISNUMERIC() reveals that +/-/e/./$ etc are considered to be a valid component of a numeric value. This is fine for FLOAT, DECIMAL and MONEY datatypes, but with an INTEGER data type, the ISNUMERIC() check is not enough to guarantee a correct answer without resorting to additional character stripping/checks.
There is no ISINTEGER() function, so Jeff Moden has written
a quick and dirty an elegant replacement that does a good job. It has been written as a table valued function, but it would be very straightforward to convert to a scalar function. I suspect this was a deliberate omission, as inline functions used against large volumes of data (as this might well be) can be very resource hungry.