The simpler way is this:
index on LTRIM(STRTRAN(address,"0123456789",""))
Richard Seidel
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
function stripHouseNumber
para anAddress
private string
string=space(0)
for i=1 to len(anAddress)
if isdigit(subs(anAddress,i,1))
else
string=string+subs(anAddress,i,1)
endif
endfor
string=alltrim(string)
return(string)
Indexing on a function isn't speedy. An alternative would be to populate a temp database with stripHouseNumber() values and index that.
Good Luck.
Ciao,
John Potter
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?
Thanks!
Hobbes (hobbes@visionfriendly.com)