Thanks for the idea, but I'm not sure how efficient that would be and also I'm not even sure how to do that without a separate select statement to create a query to parse through from sqlserver 2000 or 2005... There might be 1 million (or more) records depending on the size of the city.



>Hi Tracy,
>
>Would be an option do you parse the varChar and to separate only number and before to perform Val function?
>P.S.: Loop char to char and concatenate the number and stop wher appear other digits like "A", "B"...
>I hope this help you.
>
>
>>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?
>