Sqlite FTS3 MATCH OR

Question

I am using FTS3 in sqlite in an android project. The problem is that when I search for the word "or" the database expect more words.

"SELECT * FROM words WHERE Palabra MATCH '"+word+"'";

This works well except when the word is "or".

How can I search for the word "or" in the database?

Here is the error:

    android.database.sqlite.SQLiteException: malformed MATCH expression: [OR] (code 1)
    at android.database.sqlite.SQLiteConnection.nativeExecuteForCursorWindow(Native         Method)
    at    android.database.sqlite.SQLiteConnection.executeForCursorWindow(SQLiteConnection.java:845)
    at       android.database.sqlite.SQLiteSession.executeForCursorWindow(SQLiteSession.java:836)
    at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:62)
    at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:144)
    at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:133)

Thanks

Answer 1

To search for the word "OR" (or other MATCH expression keywords), you have to quote them (double quotes are usually used for phrase searches, and are harmless for single words):

sqlite> select * from t where t match 'NOT OR';
Error: malformed MATCH expression: [NOT OR]
sqlite> select * from t where t match '"NOT" "OR"';
TO BE OR NOT TO BE

Searching for the word "or" works just fine, because match expression keywords are case sensitive; it might be easiest for you to use only lowercase search words:

sqlite> select * from t where t match 'not or';
TO BE OR NOT TO BE