I have a project that I have a database of street addresses, and I want to get the driving directions to the street when I select a record from a Table.
So if I tap on a record in a Table and I get the address: 17 Cherry Street in txtAddress text box.
I want to search my database of directions for Cherry Street but somehow ignore the 17 in the address. I was using this code:
//Get Street Directions
Dim st as Text
Dim sqlNew As Text
st= "%" + txtAddress.Text + "%"
sqlNew="SELECT * FROM Directions WHERE (Street2) Like '" + st + "'"
rsS=App.KTM6.SQLSelect(sqlNew)
txtDirections.Text= (rsS.Field("Directions").TextValue)
The above code does not work. If I use this text below, it will display the directions for Cherry St, but this is not going to work obviously.
//Get Street Directions
Dim st as Text
Dim sqlNew As Text
st= "%" + "Cherry St" + "%"
sqlNew="SELECT * FROM Directions WHERE (Street2) Like '" + st + "'"
rsS=App.KTM6.SQLSelect(sqlNew)
txtDirections.Text= (rsS.Field("Directions").TextValue)
I was wondering how I can modify
st= "%" + txtAddress.Text + "%"
so that it will search just for the Street Name displayed in txtAddress text box?
Or is there a lay in this iOS version to ignore the numbers displayed in a textbox so I can just search the street name?
Thanks Dave. You were right, it gives the entire address including the number. That’s not what I want. I thought that it would sort of search for keywords in the txtAddress field so that it would find Cherry St alone in the database. I thought that using “LIKE” would do that.
So I am searching for 17 Cherry St and it does find just Cherry St. I just want it to ignore the 17 and just search Cherry St. I thought the code above would do that.
I think what I will do is two have two fields, one for the street number and the other for the street name. That way I can easily search the street and not have to worry about the number. Thanks for your help Dave!
[quote=368957:@Dave S]Nope…
one possible thing (and these kinds of searches can get hairy)
split the address into words
remove words that are numbers, as well as abbrevations (“E.”, “W.” “St.” “Ave” etc)
then make a compound like "where address like “%word1%” and like “%word2%” etc.[/quote]
Actually… one of the Full Text Search engines should be available. Look on the sqlite.org website for FTS for more info.
The problem is in my Text Box I have 17 Cherry St. My Table in the Database of Directions will list just Cherry St. But when I run the code below it searches for “17 Cherry St”, and does not find it, because only Cherry St is in the table.
I thought there was an SQLite Query where you could search for part of a word. So if you searched for Bo, you would get back anything that started with Bo such as Boy, Boss, Boil, etc…
I used to use it with VB6, but I can’t remember what the line was.
//Get Street Directions
Dim st as Text
Dim sqlNew As Text
st= "%" + "Cherry St" + "%"
sqlNew="SELECT * FROM Directions WHERE (Street2) Like '" + st + "'"
rsS=App.KTM6.SQLSelect(sqlNew)
txtDirections.Text= (rsS.Field("Directions").TextValue)
Greg was referring to the FTS feature in SQLite… but it requires that the database itself be created “differently” as there are internal structural differences. This might be something to look into if you have full control over the database and how it is created and deployed. However if the database is created and maintained by another system, and your app just uses it as a data source, than FTS may not be an option…
here is an “idea”… .note this is off the top of my head, and probably will not be a cut and paste solution, so consider it for “illustration purposes”
st="%"+textarea1.text+"%" // the original full address
// some of this can be done with RegEx, but that is not my forte (again, illustration)
for i=0 to 9
st=replaceall(st,str(i),"%")
next i
st=replaceall(st,".","%")
st=replaceall(st," ","%")
st=replaceall(st,"%%","%")
// st should now be "%Cherry%St%" which should be a valid "LIKES" pattern
I see no mention of FTS in your comments above. That being said, unless your app is sending queries through this other app, FTS will be available as long as the engine you are using has it available. I dont think theres anything that would stop you from creating the needed Virtual Tables. From what Ive read, the built-in sqlite library on iOS should have FTS5 available (and certainly any tool Ive used recently does).
and from what I have found… FTS is available starting with iOS11
and for prior versions required the developer to install a custom compiled version of Sqlite.
I know that up until recently … for Swift (yeah I know!)… it required custom ObjC code and bridges and all kinds of fun just to get Sqlite to work… now it is much easier starting with Xcode 9
That being said… the full address would still need to be chopped up to get the MATCH to work… seems the MATCH operator for FTS is a super version of LIKE… but CHERRY would have to be parsed out of what was being searched FOR,
[code]//Get Street Directions
Dim st as Text
Dim sqlNew As Text
Dim i as Integer
st="%"+txtAddress.text+"%" // the original full address
// some of this can be done with RegEx, but that is not my forte (again, illustration)
for i=0 to 9
st=replaceall(st,str(i),"%")
next i
st=replaceall(st,".","%")
st=replaceall(st," “,”%")
st=replaceall(st,"%%","%")
// st should now be “%Cherry%St%” which should be a valid “LIKES” pattern
sqlNew=“SELECT * FROM Directions WHERE (Street2) Like '” + st + “’”
[code]//Get Street Directions
Dim st as Text
Dim sqlNew As Text
Dim i as Integer
st="%"+txtAddress.text+"%" // the original full address
// some of this can be done with RegEx, but that is not my forte (again, illustration)
for i=0 to 9
st=replaceall(st,str(i),"%")
next i
st=replaceall(st,".","%")
st=replaceall(st," “,”%")
st=replaceall(st,"%%","%")
// st should now be “%Cherry%St%” which should be a valid “LIKES” pattern
sqlNew=“SELECT * FROM Directions WHERE (Street2) Like '” + st + “’”
I was messing around with this code. It will parse the 17 out and put it in txt2, but I really need to do the opposite. Parse the text and get rid of the numbers.
txt1.Text="17 Cherry St"
Dim i As Integer
i = Integer.Parse(txt1.Text)
txt2.Text=i.ToText
// i = 17
If I could do that, it would be all I really have to do.
Well you lost me… since what you posted has no direct relation to what I posted
My code was using the classic/legacy/current/old framework (I do not use Xojo for iOS code)
but thats not the point, the point is the “process” is the same, the implemeation is altered due to String vs Text
The code I posted REPLACES the characters 0 thru 9, the space and period with “%” then reduces pairs of “%%” to “%”
the result is a proper pattern for LIKE… Personally I think the required parsing for FTS Match would be more trouble than it is worth
I’ll bet if Kem sees this thread he will pop in with a proper RegEx solution that somewhat mimics what I posted above.
Dave, I could not figure out how to do it your way, and I was thinking that I was making it too complicated. The text field has the entire address 17 Cherry St. If I could get rid of the numbers and just have the street address only, I could run a search using my originally posted code.
The parse code above gets rid of the Text characters and keeps the numbers. I’m trying to figure out how to do the opposite… get rid of the numbers and keep the text. Thats all.
