Efficient ISNUMERIC() replacements on SQL Server?

Question

So I just spent 5 hours troubleshooting a problem which turned out to be due not only to the old unreliable ISNUMERIC but it looks like my problem only appears when the UDF in which ISNUMERIC is declared WITH SCHEMABINDING and is called within a stored proc (I've got a lot of work to do to distill it down into a test case, but my first need is to replace it with something reliable).

Any recommendations on good, efficient replacements for ISNUMERIC(). Obviously there really need to be variations for int, money, etc., but what are people using (preferably in T-SQL, because on this project, I'm restricted to SQL Server because this is a high-volume SQL Server to SQL Server data processing task)?

Answer 1

You can use the T-SQL functions TRY_CAST() or TRY_CONVERT() if you're running SQL Server 2012 as Bacon Bits mentions in the comments:

SELECT CASE WHEN TRY_CAST('foo' AS INT) IS NULL THEN 0 ELSE 1 END

SELECT CASE WHEN TRY_CAST(1 AS INT) IS NULL THEN 0 ELSE 1 END

If you're using SQL 2008 R2 or older, you'll have to use a .NET CLR function, and wrap System.Decimal.TryParse().

Answer 2

Depending on the circumstances and the performance characteristics of the validation, I sometimes use a variation of the LIKE expression instead. For example:

NOT LIKE '%[^0-9]%'

Note that this specific example is fairly naive. It doesn't guarantee that the value is valid for conversion to a particular data type. It also doesn't allow for +/- signs or decimal points if you need those.