SQL Server如何高效替代ISNUMERIC()函数？

Question

我刚刚花了5个小时排除问题，结果发现原因不仅是 不可靠的旧版 ISNUMERIC，而且看起来我的问题只会在使用 WITH SCHEMABINDING 声明 ISNUMERIC 的UDF内被调用时出现（我需要做很多工作来将其归结为一个测试用例，但我首先需要用可靠的方法替换它）。

有没有好的、高效的替代方案可以推荐给我呢？显然需要有针对 int、money 等的变体，但人们都在使用什么（最好是在T-SQL中，因为这个项目中我受限于SQL Server，因为这是一个高吞吐量的SQL Server到SQL Server数据处理任务）？

- Cade Roux

---

我还没有尝试过它，但考虑到我使用过的极其性能差的SQL UDFs（尽管需要处理数百万行数据），我倾向于尝试一下.NET CLR，并将赏金授予该答案。 - Cade Roux

所以你把赏金授予了一个在11月份就已经给出的答案？那么看起来这个赏金是浪费了，不是吗？ - Sam Schutte

我本希望悬赏能够激励某个人发布一个众所周知的好解决方案，但我并不认为浪费一半的悬赏是明智的。 - Cade Roux

好的，我觉得我的扩展存储过程很棒！ :) 嘿嘿嘿...开玩笑。 - Sam Schutte

Answer 1

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

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

- Dave Markle

---

@le dorfier：是吗？我认为这应该比除了最简单的T-SQL表达式以外的任何东西都要快。但我没有数据，只是一种直觉感觉。你能指出一些有数字的资源吗？ - Tomalak

请注意，因为这篇文章在2015年出现在首页：从SQL Server 2012开始，您可以使用TRY_CAST()或TRY_CONVERT()。 - Bacon Bits

@BaconBits：我不知道这个。谢谢！已编辑！ - Dave Markle

Answer 2

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

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

请注意，这个具体的例子相当简单。它不能保证该值可以有效地转换为特定的数据类型。如果您需要正负号或小数点，则也不允许使用它们。

请注意，这个具体的例子相当简单。它不能保证该值可以有效地转换为特定的数据类型。如果您需要正负号或小数点，则也不允许使用它们。

- RoadWarrior

---

这可能实际上是最接近我们正在替换的系统中原始逻辑的内容。 - Cade Roux

提问者表示这是一个高频任务，使用 NOT LIKE 一定无法很好地扩展。 - karlgrz

IsNumeric也无法很好地扩展，编写自己的UDF也是如此。这本质上将是一个相对较慢的检查过程。 - HTTP 410

Answer 3

另一种选择可能是使用诸如C语言之类的语言编写扩展存储过程，将其制作成DLL文件并使其可用于SQL Server。

我认为这不需要太多的代码行，而且它可能比使用.NET编写托管存储过程更快，因为您不需要加载CLR所带来的额外开销。

以下是一些信息提示： http://msdn.microsoft.com/en-us/library/ms175200.aspx 以下是一些可能适用于您的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;
}

- Sam Schutte

Answer 4

如果您期望一定范围的数字，可以按照.NET CLR路径使用正则表达式。

SQL 2005和正则表达式

- Lurker Indeed

---

链接已损坏。我认为 RegExp 在 2005-2008 上不起作用。 - marknuzz

Answer 5

通常作为一种惯例，我尽量不让未分类的数据进入数据库，因为更适合在应用程序层处理它，或者对于批量导入，使用SQL集成服务来处理，这样数据就可以从一开始就正确分类。

我以前经常这样做，通常最快的方法是编写自己的用户定义函数来验证数据是否符合您的预期格式，因为大多数情况下，调用扩展存储过程或托管代码进行简单验证的开销比在T-SQL中执行要慢。

- duckworth

---

是的，不幸的是，我必须在这些（自由格式列）上复制业务逻辑，因为数据仓库的ETL级别尚未应用该逻辑，我别无选择。 - Cade Roux

Answer 6

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

当然，如果您的数据库管理员允许这样做 :)

我的不允许 :(

- Grzegorz Gierlik

Answer 7

对于 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。

- c'est moi

---

那么你是想让我把这个放在 while 循环中，然后迭代检查每一条记录吗？这与基于集合的操作不兼容。你知道，SQL 设计的就是这种类型的操作。 - marknuzz

Answer 8

你是否会处理超出你自己（人类）语言的数字系统，例如中文等？如果是这样，我建议使用 libuninum 库。

- boost

---

我可以使用T-SQL之外的东西，我想我会使用具有定义格式的CLR解析函数。 - Cade Roux

Answer 9

实现这两个函数怎么样：

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

原始来源

- GateKiller

Answer 10

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

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

- CleanBold