SQLite and Full-Text Searches
SQLite is an interesting database technology. While not a technology we would often use on the backend, it is great for mobile projects because of its lightweight nature.
A feature many people don’t know about is its ability to perform fuzzy text searches (full text). This ability is pretty much baked into SQLite; it only requires an extension called fts5.
The first step using full-text search in SQLite is creating a virtual table. These tables are defined in a somewhat odd syntax. Using fts5 is where things start to get odd. Also, we have to denote each column indexed or not indexed.
Now we can insert data into our table using typical SQL insert statements.
Now we can sure the text using SQLite’s full-text search. The search below will find the word ‘tortor’ inside of the message column.
SQLite’s full-text searches are case-independent.
But if we try searching for just ‘tort’, it won’t be found.
You can use operators to change the behavior of search, such as performing match on just the start of a word, “tort”. You can do that using a match on “tort*”.
SQLite is pretty amazing, especially when you factor in how light of a set up it has.
