Using BETWEEN in a varchar field is not a numeric field?

Question

I am using ColdFusion 8 and SQL Server 2008 R2.

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

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

My query looks like this:

 select column from table where column between 1 and 2 

This query will not be executed because the where where column is varchar and I get a conversion error, so I need to change the where statement:

 where column between '1' and '2' 

Now, when I run such a query, it starts, but I get no results. But I know that I should see the results, because I know that many of the values ​​in the column field are within this range, which I request.

I am wondering if I can see the results due to the fact that the field is varchar and not numeric. Could this ruin my results?

In addition, we have 100,000 + records that we are viewing, will there be much success using the varchar field instead of the number field?

Answer 1

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

Answer 2

Another option

Implicit conversion is done by nvarchar () to numeric ()

The cost of operations is obvious and implicit conversion is equal, but the code is slightly less;))

• Predicate

 SELECT * FROM dbo.your_table WHERE [COLUMN] BETWEEN 1.00 AND 2.00