8

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.

quadrupleslap
  • 460
  • 1
  • 6
  • 18

2 Answers2

3

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

jspcal
  • 50,847
  • 7
  • 72
  • 76
  • 2
    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? – Britton Kerin Nov 12 '19 at 00:39
  • Not fuzzy search – Emperor Eto Jan 21 '20 at 04:39
  • 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"? – WinEunuuchs2Unix Apr 24 '21 at 00:10
  • FTS at least in the case of SQLite3 means "Full Text Search" – Can Rau Aug 14 '22 at 02:46
3

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.

sarrysyst
  • 217
  • 1
  • 8