The simpler way is this:
index on LTRIM(STRTRAN(address,"0123456789",""))
Sent by John Potter on 05/02/2002 07:52:01 AM:
Write a stripHouseNumber function and index on it:
index on stripHouseNumber(address) to temp
for i=1 to len(anAddress)
Indexing on a function isn't speedy. An alternative would be to populate a temp database with stripHouseNumber() values and index that.
Sent by Hobbes on 04/23/2002 09:34:09 PM:
Hello! I'm relatively new to the world of databases, and I'm working on VB/ASP pages that execute simple SQL against a FoxPro database to dynamically generate pages. It's a real estate database generated by 3rd party software, and I'd like users to be able to sort results based on street name (not number). However, the address field looks like this:
5838 Waveland Avenue
My understanding is that it should be simple to do this, but I can't seem to find a clear answer anywhere. Any takers?