trimming numbers out of addresses

I'm trying to create a sql statement that returns the whole address field but using a where clause that selects addresses, say, starting with the letter A and ending with the letter D. My problem is that I have numbers in the address field (house numbers) which I'm not sure how to get around in my statement.

Data like this exists in the table: "11185 N. Garden St". The numbers will range between 1 to 5 or so digits long (could probably be longer). So, doing a SUBSTRING won't exactly work unless I can figure out how to take off the number of digits prior to the space seperating the number and the street. There is also be a problem of dealing with addresses like "1183 1/2 N. Garden St." (with 1/2 in the number).

Any ideas on how to start something like this? Anyone want to help me out brainstorming this one?

run this statemet in Query analyser just replace '11185 N. Garden St' by your field name and everything will work fine.
select PATINDEX ( '%[a-z]%' , '11185 N. Garden St' ),LEFT ( '11185 N. Garden St' , PATINDEX ( '%[a-z]%' , '11185 N. Garden St' )-1 ) ,right ('11185 N. Garden St',len('11185 N. Garden St')- PATINDEX ( '%[a-z]%' , '11185 N. Garden St' )+1)

output:
7 ; 11185 ; N. Garden St

explanation:
I looking for the first occurance of any letter (btw, check to see if you server is case Sensitive or not) then I just use left and right

That's awesome man. Thanks, but do you know how that might translate into mySQL?

not really..I don't know mySQL..but try posting it in the MySql Forum..I'm sure you'll get a quick answer..it's a classic problem.