SQL Server - Select between two number ranges in a varchar field with text and numbers

Question

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?

Answer 1

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.