如何在非数字字段上查询“之间"数字数据? [英] How can I query 'between' numeric data on a not numeric field?

问题描述

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

I've got a query that I've just found in the database that is failing causing a report to fall over. The basic gist of the query:

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

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

Now, myField doesn't contain numeric data in all the rows [it is of nvarchar type]... but this query was obviously designed such that it only cares about rows where the data in this field is numeric.

这样做的问题是 T-SQL(据我所知)不会短路 Where 子句，从而导致它丢弃数据不是数字的记录，但有例外:

The problem with this is that T-SQL (near as I understand) doesn't shortcircuit the Where clause thus causing it to ditch out on records where the data is not numeric with the exception:

消息 245，级别 16，状态 1，第 1 行将 nvarchar 值/A"转换为数据类型 int 时转换失败.

如果将 myField 为数字的所有行转储到临时表中，然后查询该字段在指定范围内的行，我该怎么做才是最佳的?

Short of dumping all the rows where myField is numeric into a temporary table and then querying that for rows where the field is in the specified range, what can I do that is optimal?

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

My first parse purely to attempt to analyse the returned data and see what was going on was:

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

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

But I get the same error I did for the first query which I'm not sure I understand as I'm not converting any data that shouldn't be numeric at this point. The subquery should only have returned rows where myField contains numeric data.

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

Maybe I need my morning tea, but does this make sense to anyone? Another set of eyes would help.

提前致谢

推荐答案

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

IsNumeric only tells you that the string can be converted to one of the numeric types in SQL Server. It may be able to convert it to money, or to a float, but may not be able to convert it to an int.

改变你的

IsNumeric(myField) = 1

成为:

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

(也就是说，您希望 myField 仅包含数字，并且适合整数)

(that is, you want myField to contain only digits, and fit in an int)

编辑示例:

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

结果:

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

(1 row(s) affected)

这篇关于如何在非数字字段上查询“之间"数字数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！