Efficient ISNUMERIC() replacements on SQL Server?

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

Answers


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


Need Your Help

How to get the size of the file in a server using NSURLRequest in iOS?

ios iphone download

There is an image file in the server. I would like to know the size of the image without downloading it from the server.

Install Numpy 1.9.2 on Ubuntu from Terminal

python numpy installation ubuntu-14.04

I have Python 2.7 version and I am trying to upgrade or fresh install numpy 1.9.2 version. I am trying the upgrade option.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.