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

问题描述：

我有一个查询，我刚刚在数据库中发现失败导致报告崩溃。查询的基本要点是：如何在非数字字段上查询“数字数据之间”？

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, Line 1 Conversion failed when converting the nvarchar value '/A' to data type int.

将myField为数字的所有行转储到临时表中，然后查询该字段位于指定范围内的行时，我该做什么最佳？

我的第一个纯粹的解析，试图分析返回的数据，看看发生了什么事情是：

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

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

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

在此先感谢

派生表没有得到物化第一那么'WHERE'适用条款。它更像是一个视图，其中优化器将重写第二个像第一个那样的关系代数POV，它们是相同的。 – 2010-09-21 12:50:49

根据[此页]（http://msdn.microsoft.com/en-us/library/aa226054（SQL.80％29.aspx），没有必要显式地将'nvchar'转换为'int'。 – NullUserException 2010-09-21 12:56:00

答

则IsNumeric只能告诉您该字符串可以在SQL Server中转换为的一个数值类型。它可能会将其转换为货币或浮动，但可能无法将其转换为int。

更改您的

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)

这与案例陈述结合起来就像一个魅力。你实际上帮助我解决了一个算数溢出问题，它转换的数字对于bigint来说太长了。所以这很有帮助。 +1 – BenAlabaster 2010-09-21 13:15:08

@BenAlabaster - 非常棒 - 所以在你有机会发布之前，我回答了你的后续问题？ :-) – 2010-09-21 13:25:42

答

您必须强制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评估是保证
也不是漂亮或whizzy

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

实际上，这个问题证明了这个SQL实现不是声明式的，你告诉优化器你想要什么，它不能将你的声明转换成一个可行的执行计划，在一个真正的声明性语言中，即使条件'Convert（int，myField）在@StartRange和@EndRange之间以及IsNumeric（myField）= 1'是正确的（易于使用三元逻辑，其中'NULL AND FALSE'为'FALSE'） – MSalters 2010-09-21 13:18:47

@ MSalters：我会说BETWEEN或ISNUMERIC在SQL正在做它的声明性的事情，并决定如何最好地获取数据，用什么ORDER评估条件.BETWEEN更有可能使用索引，ISNUMERIC不是。重写垃圾数据不是垃圾数据的一部分的“声明”定义 – gbn 2010-09-21 13:32:35

Sorr哟，认为“三元逻辑”有点清楚。无论何时IsNumeric方面评估为FALSE，表达式的Convert一侧将计为NULL（错误）。一个真实的声明性语言不会认为这是一个“垃圾查询”，因为结果是非常明确的。 – MSalters 2010-09-21 13:42:20

答

不知道这是否对您有所帮助，但是我确实在某处读过使用CONVERT进行不正确的转换将始终在SQL中产生错误。所以我认为最好在where子句中使用CASE以避免CONVERT在所有行上运行

这是有效的，我不确定它是最佳的，但这比我之前至少还要进一步。谢谢。 – BenAlabaster 2010-09-21 12:53:24

答

使用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

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

相关推荐