How to fuzzy search single sqlite3 field

Question

I have a bunch of rows in a basic CRUD app that I want to let the user search for by title. SQLite3 has a full-text search solution, but is it recommended to also use that for not-full-text searches, or is there a simpler way? For example, PostgreSQL has trigrams, which function really well for this simple use-case, and perhaps SQLite3 has something similar.

I feel like this question must have been answered before, but I cannot find such an answer, so I'm sorry if this is a duplicate.

Theatrician

Answer 1

FTS is a good fit for this use-case. You can use the MATCH operator on individual columns or the entire table. See https://www.sqlite.org/fts3.html

Mycah

I didn't see any fuzzy-word type stuff in sqlite FTS3/4/5. It sounds from the mention of trigrams like that's what the OP wanted (and so do I). Is there support for fuzzy matching somewhere that I missed in there? – Benitez

Not fuzzy search – Sunlight

I found your answer looking for "Fuzzy Logic Search" which used to be in ACCPAC Plus which was a b-trieve database. In your link it talks about FTS3 and FTS4. What FTS stand for isn't explained in the link. Is it "Fuzzy Table Search"? – Lamplighter

FTS at least in the case of SQLite3 means "Full Text Search" – Sackey

Answer 2

The Spellfix1 virtual table can be used in cooperation with FTS to implement a fuzzy full text search in sqlite. This allows searching for potentially misspelled words, which (as far as I know) isn't supported by sqlite's FTS on its own.

Nemhauser