我正在使用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?
发布评论