4

Is there any way to search for a particular string in a column?

I want to search like SELECT * from email_fts WHERE email_fts MATCH 'to:"a@b.com" OR from:"c@d.com"'

Thanks in advance,

Manoj

ManojMarathayil
  • 712
  • 11
  • 28

1 Answers1

6

Make sure you create proper FTS columns in the FTS index:

CREATE VIRTUAL TABLE email_fts USING fts3(subject, body, "to", "from");

And then you can search individual FTS columns:

SELECT  rowid 
FROM    email_fts 
WHERE   "to" MATCH 'a@b.com'

UNION

SELECT  rowid 
FROM    email_fts 
WHERE   "from" MATCH 'c@d.com'

EDIT: My previous answer had an OR in the WHERE clause. Apparently sqlite doesn't support combining OR queries with MATCH conditions. The above union works.

FTS docs are here, this is one of the examples used in the docs.

http://sqlite.org/fts3.html

Samuel Neff
  • 73,278
  • 17
  • 138
  • 182
  • Not able to find a related doc. I got error like this: sqlite> .schema email_fts CREATE VIRTUAL TABLE [email_fts] USING fts3 ( [from], [subject], [body], [to], [cc], [filename] sqlite> SELECT * FROM email_FTS WHERE "to" MATCH 'a@b.com' OR "from" MATCH 'b@c. com'; SQL error: unable to use function MATCH in the requested context sqlite> – ManojMarathayil Apr 13 '10 at 10:12
  • 3
    @Manoj, I didn't realize sqlite doesn't support combining `OR` with `MATCH`. Strange. I updated the answer to use a `UNION` which works fine. If you need to combine with non-FTS data you can wrap the union in a subquery and join that on other tables. – Samuel Neff Apr 13 '10 at 13:29