Can't believe I did not know before that the Xojo SQLite-engine allows you to add extensions for things such as Levenshtein distance... just added Spellfix to my project in which I want to match titles that are very similar but might have some minor spelling mistakes (read differences).
Another problem I have is that titles may have subtitles added to the same field. Would it be wise to create a shorter title field that caps things of as a text control?
(FYI I am trying to compare the output of several search databases with our own publication registration system. If only it were as easy as comparing DOI or other identifiers... sigh)
For people that do not know what I am talking about, but are interested in similar text searches, a simplified example:
Compile the spellfix.c from Sqlite as explained here: https://julifos.wordpress.com/2018/11/13/mac-sqlite3-command-line-one-liner-load-extension/
//Create database if not already existing db = New SQLiteDatabase db.DatabaseFile = f If db.CreateDatabaseFile Then db.LoadExtensions = true //location of the created dylib Dim g As FolderItem = SpecialFolder.Desktop.Child("spellfix.dylib") //create a table Dim sql As String sql = "CREATE TABLE Articles (LucrisID INTEGER NOT NULL," +_ "Title TEXT," +_ "PRIMARY KEY(LucrisID))" App.db.SQLExecute(sql) If App.DB.Error Then System.DebugLog("DB Error: " + App.DB.ErrorMessage) Else System.DebugLog("Articles table created successfully.") End If Dim newRec As New DatabaseRecord newRec.Int64Column("LucrisID") = 1 newRec.Column("Title") = "Hello World" App.db.InsertRecord("Articles", newRec) If App.db.Error Then System.debuglog(App.db.ErrorMessage) End If Dim newRec2 As New DatabaseRecord newRec2.Int64Column("LucrisID") = 2 newRec2.Column("Title") = "Hello World!" App.db.InsertRecord("Articles", newRec2) If App.db.Error Then System.debuglog(App.db.ErrorMessage) End If //load the extension (you only need to do this once, otherwise you get an initialisation error) sql = "SELECT load_extension ('"+g.NativePath+"','sqlite3_spellfix_init')" db.SQLExecute(sql) //try it out sql As String = "SELECT * FROM Articles WHERE editdist3(Title, 'Hello Worl') < 450" Dim rs As RecordSet rs = App.db.SQLSelect(sql) if rs <> nil then Dim txt As String while not rs.EOF txt = txt + EndOfLine + rs.Field("LucrisID").Int64Value.ToText + " - " + rs.Field("Title").Value rs.MoveNext wend TextArea2.text = txt else if app.db.Error then TextArea2.text = App.db.ErrorMessage else TextArea2.text = "" end if end if end if