Geek Answers Handbook

How to implement fuzzy search using SQLite FTS3?

I already have an integrated search based on the official Android documentation and I use the following schema and SQLite query:

CREATE VIRTUAL TABLE Search USING FTS3 ( _id, name, location ); select * from Search where name MATCH ? -- where ? is the user typed exact "query" -- or if it doesn't have spaces or stars I append a star to search prefix: "query*"

I am wondering how can I extend it? to allow the following:

Say I have a few names:

  • My fancy element
  • My secret element
  • Paragraph 1
  • Your Unusual Item.

When the user enters blah in the search field, the search results show:

  • my
    • My Fancy Element
    • My secret element
  • mfi
    • M y F ancy I tem
  • fan item , fanit , fit
    • My fan cy he em
    • Your fan cy he em
  • it , item , im , itm
    • My fancy i te m
    • My secret I te m
    • I te m # 1
    • Your fancy I te m

The results should be ranked based on how good the match is, for example, if the letters are further away, they should be rated lower than the exact match, for example, for mfi : “My Unusual Item” should take last place and “MFI thingy” should take first place (if there was such an item).

Note. my min SDK is API level 10, which means that it must run SQLite 3.6.22 .

Similar functionality can be found mainly in the IDE:

+8
android sqlite search full-text-search fts3
source share
2 answers

SQLite FTS allows you to search only for whole words or for prefixing words.

There are no built-in functions for such fuzzy queries. (And the Android database API does not allow you to add custom implementations of a virtual table.)

+3
source share

I went with the weakening of my criteria to find all the beginnings of the word:

 private static String fixQuery(String query) { return query.trim().replaceAll("\\s+", "*") + "*"; }

It works very well. Not sealed, but I feel natural when I use it.

+1
source share

More articles:


All Articles