Efficient ISNUMERIC() replacements on SQL Server?

sql-server user-defined-functions isnumeric
Cade Roux picture Cade Roux · Nov 23, 2008 · Viewed 25.1k times · Source

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

Dave Markle picture Dave Markle · Nov 23, 2008

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

Related questions

How to strip all non-alphabetic characters from string in SQL Server?

How could you remove all characters that are not alphabetic from a string? What about non-alphanumeric? Does this have to be a custom function or are there also more generalizable solutions?

Read More
SQL User Defined Function Within Select

I have a user defined function in SQL called getBuisnessDays it takes @startdate and @enddate and returns the number of business days between the two dates. How can I call that function within my select? Here's what I'd like to …

Read More
How to determine the number of days in a month in SQL Server?

I need to determine the number of days in a month for a given date in SQL Server. Is there a built-in function? If not, what should I use as the user-defined function?

Read More