SQL Server 上的高效 ISNUMERIC() 替换？

Question

因此，我花了 5 个小时排除了一个问题，结果发现该问题不仅是由于 旧的不可靠 ISNUMERIC 但看起来我的问题仅在 UDF 其中 ISNUMERIC 时出现 声明为 WITH SCHEMABINDING 并在存储过程中调用（我需要做很多工作才能将其提取到测试用例中，但我的第一个需要是替换它与可靠的东西）。

有关 ISNUMERIC() 的良好、高效替代方案的任何建议。 显然，int、money 等确实需要有所变化，但是人们使用的是什么（最好是在 T-SQL 中，因为在这个项目中，我受到限制）到 SQL Server，因为这是一个大容量 SQL Server 到 SQL Server 数据处理任务）？

Answer 1

如果您运行的是 SQL Server 2012，则可以使用 T-SQL 函数 TRY_CAST() 或 TRY_CONVERT()，正如 Bacon Bits 在评论中提到的那样：

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

Answer 2

根据验证的环境和性能特征，我有时会使用 LIKE 表达式的变体。 例如：

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

请注意，这个具体示例相当幼稚。 它不保证该值对于转换为特定数据类型是有效的。 如果您需要的话，它也不允许使用 +/- 符号或小数点。

Answer 3

另一种选择可能是用 C 等语言编写扩展存储过程，将其放入 DLL 中并可供 SQL Server 使用。

我不认为这需要太多代码行，而且它可能比在 .NET 中编写托管存储过程更快，因为您不会因加载 CLR 而产生额外的监听。

这里有一个信息：
http://msdn.microsoft.com/en-us/library/ms175200。下面

是一些可能适合您的 C++ 代码：

using namespace std;

int checkNumber() {
  int number = 0;
  cin >> number;
  cin.ignore(numeric_limits<int>::max(), '\n');

  if (!cin || cin.gcount() != 1)
    cout << "Not a number.";
  else
    cout << "Your entered: " << number;
  return 0;
}

Answer 4

一般来说，作为一种实践，我尽量不要让非类型化数据进入数据库，因为它更适合在应用程序层处理它，或者批量导入在 SQL Integration Services 中处理它，以便数据从正确的类型中输入开始。

过去我不得不这样做很多次，通常最快的方法是编写自己的用户定义函数来验证数据是否采用您期望的格式，因为大多数时候调用扩展存储过程的开销或用于简单验证的托管代码比仅在 T-SQL 中执行要慢。

Answer 5

遵循 .NET CLR 路线，您可以使用正则表达式，特别是如果您期望特定范围的数字。

SQL 2005 和正则表达式

Answer 6

根据 Microsoft 支持，替换 UDF 函数的唯一有效方法是编写自己的 .NET 函数版本。

当然，如果您的数据库管理员允许的话:)。

我的没有:(。

Answer 7

您是否曾经要处理您自己（人类）语言之外的数字系统，例如中文等？ 如果是这样，我建议使用 libuninum 库。

Answer 8

如何实现这两个功能：

CREATE FUNCTION dbo.isReallyNumeric  
(  
    @num VARCHAR(64)  
)  
RETURNS BIT  
BEGIN  
    IF LEFT(@num, 1) = '-'  
        SET @num = SUBSTRING(@num, 2, LEN(@num))  

    DECLARE @pos TINYINT  

    SET @pos = 1 + LEN(@num) - CHARINDEX('.', REVERSE(@num))  

    RETURN CASE  
    WHEN PATINDEX('%[^0-9.-]%', @num) = 0  
        AND @num NOT IN ('.', '-', '+', '^') 
        AND LEN(@num)>0  
        AND @num NOT LIKE '%-%' 
        AND  
        (  
            ((@pos = LEN(@num)+1)  
            OR @pos = CHARINDEX('.', @num))  
        )  
    THEN  
        1  
    ELSE  
    0  
    END  
END  
GO  

CREATE FUNCTION dbo.isReallyInteger  
(  
    @num VARCHAR(64)  
)  
RETURNS BIT  
BEGIN  
    IF LEFT(@num, 1) = '-'  
        SET @num = SUBSTRING(@num, 2, LEN(@num))  

    RETURN CASE  
    WHEN PATINDEX('%[^0-9-]%', @num) = 0  
        AND CHARINDEX('-', @num) <= 1  
        AND @num NOT IN ('.', '-', '+', '^') 
        AND LEN(@num)>0  
        AND @num NOT LIKE '%-%' 
    THEN  
        1  
    ELSE  
        0  
    END  
END  
GO

原始来源

Answer 9

对于 SQL Server 2005 及更高版本...利用 try/catch...

declare @test varchar(10), @num decimal
select @test = '0123A'

begin try
    select @num = cast(@test as decimal)
    print '1'
end try 
begin catch
    print '0'
end catch

打印 0。

更改 @test = '01234' 或 @test = '01234.5' 并打印 1。

Answer 10

IsNumeric() 似乎对空格、“D”、“E”、美元符号和各种其他字符有问题。 我们通常想要的是告诉我们 CAST 或 CONVERT 是否会成功。 这个 UDF 虽然不是最快的解决方案，但对我来说效果很好。

create function dbo.udf_IsNumeric(@str varchar(50))
  returns int
as
begin
  declare @rtn int
  select @rtn =
    case
      when ltrim(rtrim(@str)) in('.', '-', '-.', '+', '+.') then 0
      when ltrim(rtrim(@str)) like '%[^-+.0-9]%' then 0
      else isnumeric(@str)
    end
  return @rtn
end

Answer 11

从 SQL 2012 开始，您可以使用 TRY_PARSE() 函数代替 ISNUMERIC()。

SELECT
 TRY_PARSE('123' as int) as '123'
,TRY_PARSE('abc' as int) as 'abc'