在varchar字段而不是数字字段上使用BETWEEN？ [英] Using BETWEEN on a varchar field not a numeric field?

问题描述

我正在使用ColdFusion 8和SQL Server 2008 R2。

I am using ColdFusion 8 and SQL Server 2008 R2.

我正在尝试查询一列值以获取具有某个范围内的值的行。该列应为数字，但不是。它被设置为varchar（由其他人）。超过100,000行的数据。这是一个伪造的数据样本：

I am trying to query a column of values to get rows with a value within a range. The column SHOULD be numeric, but it's not. It's setup as a varchar (by someone else). There are 100,000+ rows of data. Here's a FAKE sample of the data:

ID COLUMN
1  1
2  1.2 
3  0.9 
4  5 
5  -6

我的查询如下：

select column
from table
where column between 1 and 2

该查询不会运行，因为where语句的列是varchar，并且出现转换错误，因此我必须更改where对此的陈述：

This query won't run because the where statement's column is a varchar, and I get a conversion error, so I have to change the where statement to this:

where column between '1' and '2'

现在，当我运行这样的查询时，它会运行，但是我没有得到结果。但是我知道应该看到结果，因为我知道column字段中的许多值都在我查询的范围内。

Now, when I run a query like this, it runs, but I don't get results. But I know that I should be seeing results, because I know that many of the values in the column field are within that range I am querying.

我想知道是否由于字段是varchar而不是数字而看不到结果。可能会弄乱我的结果吗？

I am wondering if I am seeing no results due to the field being a varchar and not a numeric. Might that be messing up my results?

此外，我们正在搜索的记录超过100,000条，使用varchar字段而不是

Also, we have 100,000+ records we are searching through, would there be a big performance hit by using a varchar field instead of a numeric field?

推荐答案

您需要对结果进行转换，其中ISNUMERIC（column）= 1 AND CAST（例如，列AS的小数（10,5））在1和2之间。

这篇关于在varchar字段而不是数字字段上使用BETWEEN？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！