I propose a new built-in function for SQLite to be used for doing full text searches. The function would look like this:
-
search(PATTERN, ARG1, ARG2, ...)
The first argument is a pattern containing one or more space separated words to search for. The function returns a score based on how well the pattern matches the arguments. The score is increased by 100 for every whole-word match and is increased by 1 for every subword match.
By "whole-word" match, I mean that a word from PATTERN matches a complete word in one of the arguments. A subword match is when a word in PATTERN is part of a longer word in the arguments. For example, if a PATTERN contained the word "IS" and an argument contained the words "WISHFUL" then the "IS" would have a subword match against "WISHFUL" because "IS" is contained within "WISHFUL".
Because of difficulties dealing with case in international character sets, case would be significate for these comparisons. If you want to do a caseless search, surround each argument with a lower() function call. Or, perhaps there should be a separate function named search_nocase(...)?
By Matt Sergeant on 2003-09-03:
I prefer to see this sort of thing kept out of the database. It's fairly easy to implement full text search using either a few extra tables, or a separate database (see the perl module DBIx::FullTextSearch for an example). I don't see a reason to add this to sqlite.
By Michael Tacelosky on 2004-05-07:
We use MySQL's full text search pretty extensively, and love the fact that it's integrated and easy to use (although the search format should allow the name of the index, not requiring the list of fields as it does now: http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html)
No doubt, this is not a trivial amount of work, but for those that use it, it would be very much appreciated.
By Alain Bacon on 2004-06-25:
In the mean time, for people using SQLite in PHP (built-in now) as I do, one can register one of php's search functions into sqlite and use it in queries... or make it's own function and register it for use in sqlite's select statements.
I also did it for php's md5() function that i use to hash passwords in the database.
By Andreas Fink on 2005-07-18:
DBIx::TextIndex states to support SQLite (http://search.cpan.org/perldoc?DBIx::TextIndex)