Well that is EXACTLY what my code does…
I think James needs code that works on iOS (text) and Dave’s code use string.
James, maybe you can use split, then you will have an array with: 17, Cherry and St. as elements. Then you can decide if you want to use only Cherry or get 2 elements (Cherry) and (St.) for your search (maybe add a space between).
I appreciate that Dave, I’m just having a difficult time adapting the code to the new Framework.
Str now equals Integer.ToText
ReplaceAll is now Text.ReplaceAll
So if I run the modified code, it get a syntax error on
st=.Text.replaceall(st,Integer.ToText(i),"%")Code:
[code]st=“%”+txtAddress.text+“%” // the original full address
for i=0 to 9
st=.Text.replaceall(st,Integer.ToText(i),“%”)
next i
st=replaceall(st,“.”,“%”)
st=replaceall(st," “,”%“)
st=replaceall(st,”%%“,”%")
sqlNew=“SELECT * FROM Directions WHERE (Street2) Like '” + st + “'”[/code]
Hi Alberto,
I saw that earlier today, but I don’y know how build the array.
I’m not at my machine, I may try to do something later today or tomorrow. The idea is something like this (not tested):
Dim txt1 As Text
txt1 = "17 Cherry St"
Dim words() As Text
words = txt1.Split(" ") // words("17","Cherry","St")
words.Remove(0) // words("Cherry","St")
Dim txt2 As Text
txt2 = Text.Join(words, " ") // txt2 should be "Cherry St"
That worked perfectly Alberto!!! Thanks so much. It now allows me to do get rid of the street numbers so I can just search for the address to the actual street. Here is the full code:
[code]If txtAddress.Text="" Then
exit
Else
Dim sqlNew as Text
Dim txt1 As Text
txt1 = txtAddress.Text
Dim words() As Text
words = txt1.Split(" ") // words(“17”,“Cherry”,“St”)
words.Remove(0) // words(“Cherry”,“St”)
Dim txt2 As Text
txt2 = Text.Join(words, " ") // txt2 should be “Cherry St”
sqlNew=“SELECT * FROM Directions WHERE (Street2) Like '” + txt2 + “’”
rsS=App.KTM6.SQLSelect(sqlNew)
txtDirections.Text= (rsS.Field(“Directions”).TextValue)
End If[/code]
I really appreciate you taking the time to help.
Also thank you very much Dave for also taking the time to help me. If it was not for you guys helping me out on this great forum I would be dead in the water. I very much appreciate all the help I get from this community, and feel fortunate that it exists.
Thanks again.
I’m glad I was able to help.
Just remember that if you have something like:
" 17 Cherry St " instead of
"17 Cherry St"
You will have strange results (first array element will not be the number 17).
So you better check if the first word is a number and maybe trim the extra spaces.
Thanks Alberto. I will Trim the text. Thanks again for all your help!
st=.Text.replaceall(st,Integer.ToText(i),"%")Leading “.” after the = perhaps?
[quote=369042:@Dave S]
st=.Text.replaceall(st,Integer.ToText(i),"%")
Leading “.” after the = perhaps?[/quote]
Good catch, also James code should be:
st=st.ReplaceAll(i.ToText,"%")Dave, I was able to change your code to use text instead of string:
st="%"+"17 Cherry St."+"%" // the original full address, using 17 Cherry St. as example
// some of this can be done with RegEx, but that is not my forte (again, illustration)
For i=0 To 9
st=st.ReplaceAll(i.ToText,"%")
Next i
st=st.ReplaceAll(".","%")
st=st.ReplaceAll(" ","%")
st=st.ReplaceAll("%%","%")
// st should now be "%Cherry%St%" which should be a valid "LIKES" pattern
The only ‘problem’ I can see is that st start with %, then 1 change to %, 7 to $ and " " to %, so st ends as “%%Cherry%St%”. I don’t know if having a double % makes a difference. Just in case, I did this:
While st<>st.ReplaceAll("%%","%")
st=st.ReplaceAll("%%","%")
Wend
That is what that last statement is supposed to “fix”
st=st.ReplaceAll("%%","%")but if it has to be done that way, this would be better
While Instr(st,"%%")>0 /// yeah I'm sure there is a "TEXT" version, but you get the idea
st=st.ReplaceAll("%%","%")
Wenddoing the replaceAll twice with a comparison is “expensive”
The thing is that the st has 4 % at the beginning, so when ReplaceAll %% change to % it finds 2 ‘blocks’ of %% and change that to 2 blocks of single % and it ends with %% (original %%%%).
I guess is the street number has 4 digits, then it could end with 6 % and the ReplaceAll will clean that down to 3 %.
My question is if there is any difference having a st value that start with 1 % or multiple %. If %Cherry%St% works the same as %%Cherry%St% or %%%Cherry%St%, then the While…Wend is not needed.
This is the Text version:
While st.IndexOf("%%")<>-1
st=st.ReplaceAll("%%","%")
Wend
Then in my opinon REPLACEALL has a bug… hence the word “ALL” in the command
but even the “old framework” doesn’t do “All”…
and as to “does it matter”… I just tested it in Tadpole, and no it doesn’t matter
Thank you Dave, good to know it doesn’t matter to have more than 1 %.
And for the replace, I have no experience with the command in programming languages, only on text editors and other programs like that. Those programs behave the same as the text.ReplaceAll in Xojo, for example if the text has 4 %, it finds the first 2, replace that with one, but moves to next place/position/character, that means that find %% in original position 3 and 4 (1 based, I think Xojo in this case is 0 based), and replace the 2 % with 1 %, so in the end the result is a file with %%.
I remember sometimes getting files with extra whitespace, and I had to find/replace several times for 2 blanks and replace with 1, until the editor said “Can’t find anymore”.
If I understand Xojo correctly, Replace only change the first instance it finds and ReplaceAll replace every instance.
Edit: I just did the same test with Xojo and TextEdit on Mac
For example in Xojo:
Dim result As String
result = ReplaceAll("%%17%%%%%Cherry%%%St%", "%%", "%")
// returns "%17%%%Cherry%%St%"
On Mac’s TextEdit:
%%17%%%%%Cherry%%%St%
Find %%, Replace with %, click All
Result: %17%%%Cherry%%St%
I had to run the replace All 2 more times to get %17%Cherry%St%
My guess is that a ReplaceAll that generates the same pattern again is not common, so these apps just find the match and goes to next match without checking if the replace created a new match.
FYI… the accepted answer won’t always work… it make a dangerous assumption… and that is that only the first “word” needs to be removed,
what if it were “123 Cherry St. #14”?
but then my solution fails with “123 14th St.”
Hi Dave, I understand my solution is not universal and only remove the first “word”, if it were “123 Cherry St. #14” the result will be “Cherry St. #14”, that’s because the text.split will create a text array with (123,Cherry,St.,#14) as elements and remove (0) and that’s 123.
But I don’t understand why you say that your solution fails with “123 14th St.”, are you referring to the code that replace the numbers with %? I get “%Cherry%St%#% and that could easily be fixed with a ReplaceAll “#”,”%" to end with “%Cherry%St%” (using the while)
I think we just trying to help James and he needs to find the right solution for him. Yours is much better because I don’t know exactly what James will do with the search pattern/database, I don’t have much experience with that.
“123 14th St.”, becomes “%%%%%%th St%” where for his purpose it would need to resolve to “%14th%St%”
numerical street designations, additon of bldg #'s or apartment #'s could skew the result as well
alot depends on how accurate the result needs to be, and equally important how consistent the address information is.
I worked for a large healthcare company here in the US… (It just got bought out by a DRUG STORE of all things!), but I digress
We had millions of addresses in various databases, for Doctors, Hospitals, Clinics as well as the patients, this information came from external sources, clients, keypunched by our own people etc. But the systems I maintained and wrote had to do operations similar to this, and we had entire ORACLE SQL stored procedures whos only operation was to normalize Addresses
Ah, that makes sense, sorry I didn’t understand before. English is not my first language.
When I first read your comment I read it as: if I put “123 Cherry St. #14” my solution will report “123 14th St.” 
I guess using 123 and 14 in both examples make me understand that the second was the result from the first and I was not able to understand why.
I agree, addresses are very hard to deal with. And if you go international, a nightmare. In Spain they list first the street name then the number. Zip goes before the City.
Interesting discussion.
Would it not be possible to use the isnumeric function to identify the numeric components once the string has been split into individual words and then and leave them off the address? Perhaps it could also be used to normalise addresses from different countries etc. into a standard format?
I think IsNumeric doesn’t work with iOS. I guess there are other options to do what you say but I don’t know how and maybe it depends on your data.
[quote=369071:@Philip Cumpston]Interesting discussion.
Would it not be possible to use the isnumeric function to identify the numeric components once the string has been split into individual words and then and leave them off the address? Perhaps it could also be used to normalise addresses from different countries etc. into a standard format?[/quote]
The problem is with STREET NAMES that are numeric in nature.
isNumeric("14th") is true