代码之家 › 专栏 › 技术社区 › BenAlabaster

如何在非数字字段上查询“between”数字数据?

short-circuiting sql-server-2000 tsql

7

BenAlabaster · 技术社区 · 14 年前

我刚在数据库中发现一个查询失败,导致报表崩溃。查询的基本要点:

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包含数字数据的行。

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

提前谢谢

4 回复 | 直到 14 年前

1

6

Damien_The_Unbeliever 14 年前

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)

2

3

gbn 14 年前

您必须强制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

第一种技术是“中间物化”:它强制在工作台上进行排序。
第二个依赖于案例顺序评估得到保证
既不漂亮也不嘶嘶

SQL是声明性的:你告诉乐观者你想要什么,而不是怎么做。上面的技巧迫使事情按一定的顺序进行。

3

1

Sachin Shanbhag 14 年前

不确定这是否对您有帮助,但我在某个地方读到过,使用convert的不正确转换总是会在SQL中生成错误。所以我认为最好在WHERE子句中使用CASE,以避免将转换为在所有行上运行。

4

1

Samuel Neff 14 年前

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