How to use MATCH Keyword for multi columns in SQLite?

Question

I created a table using fts3. I used following query to get a result

      String query = "SELECT "+ID+" as _id," + 
        NAME +  
        " from " + TABLENAME +
        " where " +  NAME + " MATCH '" + inputKeyword + "';";
        Log.w(TAG, query);

Which gives the list of Names based on the inputKeyword.

But it doesn't work for multi columns,

    String query = "SELECT "+ID+" as _id," + 
        NAME +  
        " from " + TABLENAME +
        " where " +  NAME + " MATCH '" + inputKeyword + "' AND "+ EMAILADDRESS + " MATCH '"+emailaddress+"';";

Which doesn't give any results and also saying you can't use MATCH in this query. How to do that?

`EMAILADDRESS + '"+emailaddress+"';";` is this bit correct? I am seeing double `;` and `'"+emailaddress+"';` — kabuto178, Oct 21 '13 at 18:20
Take a look at this (maybe duplicate) question: http://stackoverflow.com/questions/4057254/how-do-you-match-multiple-column-in-a-table-with-sqlite-fts3/14593298#14593298 — r0-, Oct 21 '13 at 18:41
Possible duplicate of [How do you match multiple column in a table with SQLite FTS3?](https://stackoverflow.com/questions/4057254/how-do-you-match-multiple-column-in-a-table-with-sqlite-fts3) — amukhachov, Jul 03 '17 at 14:34

CL. · Answer 1 · 2014-11-14T16:40:21.397

1

Your query looks like this:

SELECT ID as _id, Name
FROM TableName
WHERE Name MATCH 'xxx' AND EmailAddress'yyy'

This lacks the MATCH operator for the second column. What you want should look like this:

SELECT ID as _id, Name
FROM TableName
WHERE TableName MATCH 'Name:xxx EmailAddress:yyy'

edited Nov 14 '14 at 16:40

answered Oct 21 '13 at 19:57

CL.

173,858
17
217
259

This will not work. FTS only allows one 'MATCH' per select statement and will throw an error for the syntax you described. The question staaar linked to has a working solution; use that instead. – pbuchheit Nov 14 '14 at 16:35
Corrected. (And the linked question asks for matches in *any* column, not *all*.) – CL. Nov 14 '14 at 16:41

score 0 · Answer 2 · answered Mar 09 '15 at 12:16

0

Apparently sqlite doesn't support combining OR queries with MATCH conditions. union works

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

UNION

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

answered Mar 09 '15 at 12:16

Simon K. Gerges

3,097
36
34

How to use MATCH Keyword for multi columns in SQLite?

2 Answers2