Search code examples
sqlitefts3

SQLite fts3: search for a string in a column

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:"[email protected]" OR from:"[email protected]"'

Thanks in advance,

Manoj

Solution

  • 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 '[email protected]'

UNION

SELECT  rowid 
FROM    email_fts 
WHERE   "from" MATCH '[email protected]'

    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