Hello, I have a ListBox filled with names stored in an SQLite DB. If I digit the full name I’m able to filter it and see only the result, I’m wondering how to search by using a partial word (i.e. just 3 or 4 letters of the name). I read about MATCH and LIKE but could not apply them to my app.
Thank you
What have you tried to do? MATCH is easy to use.
You create a virtual table first (SQLite FTS5 Extension). Then you use a MATCH:
dim PreparedForSearch as SQLPreparedStatementMBS = SQLiteIndexDBMBS.Prepare("SELECT docid FROM bodyindex WHERE messagebody MATCH ?")
PreparedForSearch.BindType(0, PreparedForSearch.kTypeString)
SearchString = ReplaceAll(SearchString, """", """""")
dim data as RowSet = PreparedForSearch.SelectSQLMT("'*'" + searchstring + "'*'")
If data <> Nil Then
While Not data.AfterLastRow
ResultIDs.Add data.Column("docid").StringValue
data.MoveToNextRow
Wend
data.Close
End If
Return ResultIDs
To get best results, please, provide a simple project with the data base that holds, say city names that ends like
dorf, Offendorf
city, ?
ville, Lunéville, Thionville, …
bourg, (Offenbourg/Offenburg, Strasbourg, Strasburg, …)
Heim, Vendenheim, Molsheim, Rosheim, Marlenheim
etc.
Google maps around Strasbourg (both France and Germany) will give you city names with these ending…
So we can run it and found (more or less) easilly a solution / advice…
Here’s the link to the file and the DB: Smash
Sorry it is in Italian but I followed your indication in using German towns.
I’m only interested in searching the right ListBox which, eventually, should be filled with names and ids (i.e. D1, D2,).
Thanks for any heòp
In fact they are both; some are French (around Strasbourg) and some are German…
Google Maps opens displaying Strasbourg… (where I am).
I will look and report what I found later today.
Did you try with the virtual table? There is nothing really complicated to using MATCH.
OK. Here’s some news:
a. two images are missing; we do not care, compile in the IDE…
b. The TABLE named “bonif” is empty:
I get Nil in the second line below:
bql = "SELECT * FROM bonif"
mrz = DB.sqlSelect(bql)
Yes, I moved the sqlite file to Documents (in the right nested folders found in App.Opening).
I found the TABLE with the names of the cities (pers).
I have to go; I will come back later, sorry.
Yes, I do not care about the table named bonif (I won’t search it).
Images are compiled in the IDE.
Thank you, anyway, for your help
Yes, I am trying it and I also went along with the example in the help.
Doing exactly what the help says (changing names of the DB and tables, though) I get an error which I show in the picture.
Either I’m doing something wrong (most likely) or there is a glitch in the help
You need SelectSQL instead of SQLSelect.
Sorry I’m late.
I made it. Thanks a lot
