>I need to pull a range of records based on the street number. The number field is character. The problem occurs when a letter was entered in the number field as well as in 300A, 3505B, etc. Both of the examples below return the error:
>
>Conversion failed when converting the varchar value '3505A ' to data type int.
>or:
>Error converting data type varchar to numeric.
>
>

>select * from omain where 1100 < number and 3000 > number
>select * from omain where cast((number) as decimal(10,0)) > 1000 and cast((number) as decimal(10,0)) < 3000
>

>
>Recommendations?

Better if you uses one or more LIKE pattern,
you have a chance to optimize it with a index on the number field

select 'FOUND' WHERE '201-1AB' LIKE '[12][-0-9][-0-9][-0-9]%'

To extract numbers from a string and to use them as a filter it is not a good design.