how to search for whole words efficiently in a sqlite db

Question

Hi, I have a table that has a title column. I want to search for whole words like foo. so match " hi foo bye" o "foo", but not "foobar" or "hellofoo". Is there a way without changing the table structure to do this? I currently use 3 like queries, but it is too slow, I have " select * from articles where title like '% foo' or title like 'foo %' or title = 'foo' or title like '% foo %';
There has got to be a better way to do this?

Answer 1

spontaneous answer:

use the regexp operator instead of the like operator.

EDIT I just realised that regexp is not always included with SQLite. You might have to compile you own ... in other words, it's not there by default ..

EDIT2

here's a working Perl sample ..

#!/usr/bin/perl -w
use strict;
use Data::Dumper;
use DBI;

# connect to the DB
my $dbh = DBI->connect("dbi:SQLite:dbname=dbfile","","");

# create ugly, pureperl function 'regexp'
# stolen from http://d.hatena.ne.jp/tokuhirom/20090416/1239849298
$dbh->func(   "regexp"
            , 2
            , sub { my ( $pattern, $target ) = @_;
                         utf8::decode($pattern);
                         utf8::decode($target);
                         $target =~ m{$pattern} ? 1 : 0;
              }
            , "create_function" );
# drop table, if it exists
$dbh->do('drop table if exists foobar');
$dbh->do('create table foobar (foo varchar not null)');
my $sth=$dbh->prepare('insert into foobar (foo) values (?)');
while (<DATA>) { chop;$sth->execute($_); }
#query using regexp
my $a= $dbh->selectall_arrayref( 'select foo '
                                .'from foobar '
                                .'where foo regexp "^foo$|^foo\s+.*|.*\W+foo\W+.*|.*\W+foo$"'
                               );
print join("\n", map {$_->[0];} @{$a})
__DATA__
foo
foo
barfoo
foobarfolo
sdasdssds bar dasdsdsad
dasdsdasdsadsads foo! dasdasdasdsa

Answer 2

You might be interested in a search indexer like lucene, ferret, or sphinx. These would run as separate processes that would index your data for fast searching where stemming, etc. can be configured.

Alternatively, depending on your data, you could just return all results that contain "foo" in any context and then filter them with regular expressions or such outside of the database. This might be an improvement depending on the characteristics of your data.

Answer 3

There are various regexp libraries that you can include in your iPhone application by linking to them in your build.

See this Stackoverflow question for further info.