SQL Server - Select between two number ranges in a varchar field with text and numbers
sql, sql-server-2000, sql-server-2005, sql-server-2008, sql-server-2008-r2
Solution
If you want to do a numeric comparison, the only way to guarantee no error is to use the `CASE` statement. This is the only SQL statement that guarantees the order of valuation (when not used with aggregation functions).
So, here is code that will work:
select *
from t
where (case when isnumeric(col) = 1 then cast(col as bigint) end) between 20 and 100
Note that if your numbers contain decimal points, then you will want to use `DECIMAL` or `FLOAT` instead.
Problem
I have a table with ranges of numbers from 0-2000000. I can use a between statement to get the records of things if I do a BETWEEN 50 AND 100. That works. But the problem I'm running into is since this column is a varchar if there are any records with a something in the string as "1FD32", I can't select between it. SQL server can't convert the "BETWEEN 50 AND 100" because it is looking for an int. How can i get all the records between these ranges, and ignore any with letters in them?