Who's Smart Enough To Figure This Out?

Hello All,





I've been trying to get a range of values out of my SQL Server 2000 db without sucess. The field in question has the data type of char(8) and looks like this:





House_numbr_pub (leading spaces in front of each value)


140A


140


141


142


143


144


145


146


147


148


149


150


151


.


.


.


14500


.


.


.





Does anyone know how write a sql statement that will return 140-150, but excluding the ' 14500' and 100-1000. I tried the following where clause to return a range between 100-1000.





WHERE (cook.STREET_PUB LIKE 'lincoln%') AND (LEN(LTRIM(cook.HOUSE_NUMBR_PUB)) BETWEEN 3 AND 4) AND (


(LTRIM(cook.HOUSE_NUMBR_PUB) >= '100') and (LTRIM(cook.HOUSE_NUMBR_PUB) <= '1000') )





This where clause only return two records (100 and 1000). I want it to return 100-1000.





I also tried the following where clause:





WHERE LTrim(cook.HOUSE_NUMBR_PUB) like '1[45][0-9]'


OR


LTrim(cook.HOUSE_NUMBR_PUB) like '1[45][0-9]'





However, building this on the fly with .net will take some effort if someone is trying to search range 1-10000000.








Please Help,





James