在SQL Server上有效的ISNUMERIC()替换? [英] Efficient ISNUMERIC() replacements on SQL Server?

问题描述

因此，我只花了5个小时对问题进行了排查，这不仅是由于

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

有关ISNUMERIC()的良好，高效替代品的任何建议.显然确实需要int，money等的变体，但是人们在使用什么(最好是在T-SQL中使用，因为在此项目中，我限于使用SQL Server，因为这是大量的SQL Server到SQL Server的数据处理任务)?

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

推荐答案

如果您正在运行SQL Server 2012(如Bacon Bits注释中所述)，则可以使用T-SQL函数TRY_CAST()或TRY_CONVERT(). >

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

如果您使用的是SQL 2008 R2或更早版本，则必须使用.NET CLR函数，并包装System.Decimal.TryParse().

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

这篇关于在SQL Server上有效的ISNUMERIC()替换?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！