4

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 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

My query looks like this:

select column
from table
where column between 1 and 2

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.

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?

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?

Leigh
  • 28,765
  • 10
  • 55
  • 103
Evik James
  • 10,335
  • 18
  • 71
  • 122
  • *many of the values in the column field are within that range I am querying* IF they were stored as numbers, yes. But since they are stored as `varchar` the database compares them as strings, *essentially* comparing the ascii values of the characters - not the overall number those characters represent. So for example, the string value `'12'` is `between '1' and '2'`. – Leigh Nov 08 '12 at 22:49
  • 1
    Why don't you just update the table so the column *is* a numeric? If the data is numeric and the usage of it is numeric, it ought to be stored as a numeric. Don't deal with a side-effect of the problem hear, treat the actual problem. – Adam Cameron Nov 09 '12 at 07:09
  • 1
    Are *all* of the column's values *actually* simple "should be numeric" values, or will some rows contain more problematic data? – Damien_The_Unbeliever Nov 09 '12 at 09:20

2 Answers2

8

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

Eli Gassert
  • 9,745
  • 3
  • 30
  • 39
  • 3
    If you really need to search on this data, you're better off storing as numeric. All this casting/converting will be expensive for SQL. If you can't edit the actual table, consider creating an indexed view that does the casting and converting for you so you can query against it. – Eli Gassert Nov 08 '12 at 21:01
  • 2
    Of course, `ISNUMERIC() = 1` is *no* guarantee that the column value can be cast to `decimal(s,p)` (for any values of `s` and `p`), and SQL Server may re-order operations such that it attempts the `CAST` before it's even *checked* the `ISNUMERIC()` return value. – Damien_The_Unbeliever Nov 09 '12 at 09:19
1

One more option

Implicit transformation is carried out nvarchar() into numeric()

Cost of operations obvious and implicit transformation equals, but code a little bit it is less;))

  • Predicate

enter image description here

SELECT *
FROM dbo.your_table
WHERE [COLUMN] BETWEEN 1.00 AND 2.00
Aleksandr Fedorenko
  • 16,594
  • 6
  • 37
  • 44