CODEWITHSUNDEEP
sqlitefull-text-search
Hamish Downer
Hamish Downer

Reputation: 17087

sqlite with full text search doesn't find results when space in query

I'm playing with notmuch-abook which uses sqlite3 to store names and email addresses. The sqlite table uses full text search, being created by:

CREATE VIRTUAL TABLE AddressBook USING fts4(Name, Address);

I have entries for myself with names "Hamish", "Hamish D" and "Hamish Downer". The query

select * from addressbook where addressbook match 'hamish';

Finds them all. However

select * from addressbook where addressbook match 'hamish d';

Finds only the entries with the exact name "Hamish D" but does not find the entries with name "Hamish Downer". I can get what I expect with:

select * from addressbook where name like 'hamish d%';

But I'd like to use the match version to match across both columns. Any idea what's going on here? Or how to get match to work as I want?

Upvotes: 1

Views: 1889

Answers (1)

CL.
CL.

Reputation: 180270

Read the documentation.

The query

... addressbook match 'hamish d'

finds records that containt the two words hamish and d. You probably want to search for the phrase hamish d instead, which you can do with

... addressbook match '"hamish d"'

To search for prefixes, use *:

... addressbook match '"hamish d*"'

Upvotes: 6

Related Questions