在 varchar 字段而不是数字字段上使用 BETWEEN?

本文介绍了在 varchar 字段而不是数字字段上使用 BETWEEN?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在使用 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'

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

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?

推荐答案

需要对结果进行 CAST WHERE ISNUMERIC(column) = 1 AND CAST(column AS decimal(10,5)) BETWEEN 1 AND 2 例如.

You need to CAST the results WHERE ISNUMERIC(column) = 1 AND CAST(column AS decimal(10,5)) BETWEEN 1 AND 2 for example.