我如何查询“之间”非数字字段上的数字数据？

Question

我刚刚在数据库中发现了一个查询失败，导致报告失败。查询的基本要点：

Select *
From table
Where IsNull(myField, '') <> ''
And IsNumeric(myField) = 1
And Convert(int, myField) Between @StartRange And @EndRange

现在，myField 并不在所有行中包含数字数据[它是 nvarchar 类型]...但是这个查询显然是这样设计的：它只关心该字段中的数据所在的行数字。

问题在于，T-SQL（据我所知）不会短路Where子句，从而导致它放弃数据非数字的记录，但有例外：

Msg 245，Level 16，State 1、1号线 将 nvarchar 值“/A”转换为 int 数据类型时转换失败。

无法将 myField 为数字的所有行转储到临时表中，然后查询该字段在指定范围内的行，什么我可以做到这一点吗？

我的第一次解析纯粹是为了尝试分析返回的数据并查看发生了什么：

Select *
From (
   Select *
   From table
   Where IsNull(myField, '') <> ''
   And IsNumeric(myField) = 1
) t0
Where Convert(int, myField) Between @StartRange And @EndRange

但是我得到了与第一个查询相同的错误，我不确定我是否理解，因为我没有转换任何不应该转换的数据此时不是数字。子查询应该只返回 myField 包含数字数据的行。

也许我需要早茶，但这对任何人都有意义吗？另一双眼睛会有所帮助。

提前致谢

Answer 1

使用 CASE 语句。

declare @StartRange int
declare @EndRange int

set @StartRange = 1
set @EndRange = 3

select *
from TestData
WHERE Case WHEN ISNUMERIC(Value) = 0 THEN 0
            WHEN Value IS NULL THEN 0
            WHEN Value = '' THEN 0
            WHEN CONVERT(int, Value) BETWEEN @StartRange AND @EndRange THEN 1
            END = 1

Answer 2

IsNumeric 仅告诉您该字符串可以转换为 SQL Server 中的数字类型之一。它可能能够将其转换为货币或浮点数，但可能无法将其转换为整数。

将您的更改

IsNumeric(myField) = 1

为：（

not myField like '%[^0-9]%' and LEN(myField) < 9

也就是说，您希望 myField 仅包含数字，并适合 int）

编辑 示例：

select ISNUMERIC('.'),ISNUMERIC('£'),ISNUMERIC('1d9')

结果：

----------- ----------- -----------
1           1           1

(1 row(s) affected)

Answer 3

您必须强制 SQL 按特定顺序计算表达式。
这是一个解决方案

Select *
From ( TOP 2000000000
   Select *
   From table
   Where IsNumeric(myField) = 1
   And IsNull(myField, '') <> ''
   ORDER BY Key
) t0
Where Convert(int, myField) Between @StartRange And @EndRange

和另一个

Select *
From table
Where

CASE
   WHEN IsNumeric(myField) = 1 And IsNull(myField, '') <> ''
   THEN Convert(int, myField) ELSE @StartRange-1
END Between @StartRange And @EndRange

• 解决方案。第一种技术是“中间物化”：它强制在工作表上进行排序。
• 第二个依赖于 CASE ORDER 评估得到保证 SQL
• 既不是漂亮的，也不是精巧的

SQL 是声明性的：你告诉优化器你想要什么，而不是如何去做。上述技巧迫使事情按照一定的顺序完成。

Answer 4

不确定这是否对您有帮助，但我确实在某处读到，使用 CONVERT 进行不正确的转换总是会在 SQL 中生成错误。所以我认为最好在 where 子句中使用 CASE 以避免 CONVERT 在所有行上运行