如何根据字段是否可以转换为数字进行过滤？

Question

我有一份已经使用了很长一段时间的报告 - 事实上，公司的发票系统很大程度上依赖于这份报告（免责声明：我没有写它）。过滤基于 VarChar(50) 类型的字段是否位于用户传入的两个数值之间。

问题在于，现在过滤数据的字段不仅具有简单的非数字值，例如“/A”、“TEST”和大量其他非数字数据，而且还具有似乎是违背我能想到的任何类型的数字转换。

以下（简化的）测试查询演示了失败：

Declare  @StartSummary Int,
         @EndSummary Int

Select   @StartSummary = 166285,
         @EndSummary = 166289

Select   SummaryInvoice
From     Invoice
Where    IsNull(SummaryInvoice, '') <> ''
And      IsNumeric(SummaryInvoice) = 1
And      Convert(int, SummaryInvoice) Between @StartSummary And @EndSummary

我还尝试使用 bigint、real 和 float 进行转换，但都给出了类似的错误：

消息 8115，第 16 级，状态 2，第 7 行 算术溢出错误转换 表达式转换为数据类型 int。

我尝试过其他更大的数字数据类型，例如 BigInt，但出现相同的错误。我还尝试使用子查询来回避转换问题，方法是仅提取具有数字数据的字段，然后在包装器查询中转换这些字段，但随后我收到其他错误，这些错误都是主题的变体，表明存储在SummaryInvoice 字段无法转换为相关数据类型。

除了仅将具有数字 SummaryInvoice 字段的记录提取到临时表然后查询临时表之外，是否有任何一步解决方案可以解决此问题？

编辑：这是我怀疑导致问题的字段数据：

发票摘要

<小时>

11111111111111111111111111

IsNumeric 表明此字段是数字 - 它就是。但尝试将其转换为 BigInt 会导致算术溢出。有什么想法吗？这似乎不是一个孤立的事件，似乎有许多记录填充了导致此问题的数据。

Answer 1

看来您会遇到 ISNUMERIC 函数的问题，因为如果可以转换为任何数字类型（包括 .、、、e0 等）。如果数字长度超过 2^63-1，则可以使用 DECIMAL 或 NUMERIC。我不确定您是否可以使用 PATINDEX 对 SummaryInvoice 执行正则表达式查找，但如果可以，那么您应该尝试以下操作：

SELECT SummaryInvoice
FROM Invoice
WHERE ISNULL(SummaryInvoice, '') <> ''
AND CASE WHEN PATINDEX('%[^0-9]%',SummaryInvoice) > 0 THEN CONVERT(DECIMAL(30,0), SummaryInvoice) ELSE -1 END
BETWEEN @StartSummary And @EndSummary

Answer 2

您无法保证 WHERE 子句过滤器的应用顺序。

一种将内部和外部分离的丑陋选择。

SELECT
   *
FROM
    (
    Select   TOP 2000000000
             SummaryInvoice
    From     Invoice
    Where    IsNull(SummaryInvoice, '') <> ''
    And      IsNumeric(SummaryInvoice) = 1
    ORDER BY SummaryInvoice
    ) foo
WHERE
    Convert(int, SummaryInvoice) Between @StartSummary And @EndSummary

另一个使用 CASE

Select   SummaryInvoice
From     Invoice
Where    IsNull(SummaryInvoice, '') <> ''
    And     
    CASE WHEN IsNumeric(SummaryInvoice) = 1 THEN Convert(int, SummaryInvoice) ELSE -1 END
          Between @StartSummary And @EndSummary

YMMV

编辑：问题更新后

1. 使用十进制（38,0）而不是 int
2. 将 ISNUMERIC(SummaryInvoice) 更改为 ISNUMERIC(SummaryInvoice + '0e0')

Answer 3

AND 与 IsNumeric(SummaryInvoice) = 1 时，在 SQL Server 中不会短路。

但也许您可以使用

AND (CASE IsNumeric(SummaryInvoice) = 1 THEN Convert(int, SummaryInvoice) ELSE 0 END)
@StartSummary 和 @EndSummary 之间

Answer 4

您的第一个问题是修复数据库结构，以便不良数据无法进入该字段。您正在将创可贴贴在需要缝合的伤口上，并想知道为什么伤口无法愈合。

数据库重构并不好玩，但是当出现数据完整性问题时就需要这样做。我假设您实际上并没有向某人开具 11,111,111,111,111,111,111,111,111 或“测试”的发票。因此，不要允许输入这些值（如果您无法将结构更改为正确的数据类型，请考虑使用触发器来防止输入错误数据）并删除您所拥有的错误值。