Yes, Full Text Search!

Updated 2009-08.

You wouldn't expect Full Text Search from a library with such a lightweight footprint, but it's been supported by SQLite since 2006-Oct-9, Version 3.3.8. And it's pretty fast - the database has over 16,000 fortunes/quotes. (try searching "the" to see how fast it is)

Setting up the Table

Create/open the database and load the FTS2 module:
>> sqlite3 test.db3
SQLite version 3.3.17
Enter ".help" for instructions
sqlite> SELECT load_extension('fts2.dll');
Create the table with the CREATE VIRTUAL TABLE statement and INSERT just as you would a normal table:
sqlite> CREATE VIRTUAL TABLE fortune using FTS2(class, text);
sqlite> INSERT INTO fortune (class,text)
   ...> VALUES ('funny',
   ...> 'micro$oft == secure');


Use the MATCH operator to do the full text search by column. The default is to return documents containing all terms:
sqlite> select class,text from fortune where text MATCH 'secure micro$oft';
funny|micro$oft == secure
sqlite> select class,text from fortune where text MATCH 'nomatch micro$oft';

Web Page Results

The snippet function is perfect for your web site search page; pass the table name and it returns a portion of the document with the search term highlighted for free! As with all SQLite tables you can hook into the record with it's ROWID:
sqlite> .mode line
sqlite> select ROWID,class,snippet(fortune) from fortune
   ...> where text MATCH 'secure';
           rowid = 2
           class = funny
snippet(fortune) = micro$oft == <b>secure</b>
To change the highlighting markup pass the optional second and third parameters to snippet():
sqlite> .mode list
sqlite> select ROWID,class,
   ...> snippet(fortune,'<strong class=''red''>','</strong>')
   ...> from fortune where text MATCH 'secure';
2|funny|micro$oft == <strong class='red'>secure</strong>

There are a couple of drawbacks - the module does not currently support result ranking or fuzzy search. And you're obviously not going to do all of this on the command line! Get the SQLite ADO.NET 2.0 Provider for that.


Search the fortune /quotes database at kuujinbo.info:

Ajax tooltips: clueTip : A jQuery Plugin