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)?

Accepted Answer

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().

Efficient ISNUMERIC() replacements on SQL Server?

Solution

Problem