How to display the word to starts with apple instead of displaying every apple found using FTS?

Question

I am currently using the Sample from the SearchableDictionary which uses FTS, however when I enter the word "apple" it displays all apple words instead of only the word that starts with apple. I would like to know if there are anyway to display a list eg) 1) Apple 2) Apple pie

instead of 1) Adam's apple 2) Apple 3) Apple pie 4) Big apple

using android FTS.

According to what I understand from FTS is that it uses MATCH instead of LIKE and it functions like the LIKE %something% of the normal SQLite Query

I would appreciate it very much if someone can tell me how should I do it.

Thank you.

Answer 1

This is what I did, inside the DictionaryDatabase.java file of the SearchableDictionary. I modified some of the method codes into these:

     public Cursor getWordMatches(String query, String[] columns)
     {
        String selection = KEY_WORD + " >='" + query +"'"; 
        return query(selection, columns);
     }


     private Cursor query(String selection, String[] columns)
     {

            SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
            builder.setTables(FTS_VIRTUAL_TABLE);
            builder.setProjectionMap(mColumnMap);

            Cursor cursor = builder.query(mDatabaseOpenHelper.getReadableDatabase(),
                   columns,selection,null, null,  null, KEY_WORD );

            if (cursor == null) {
                return null;
            } else if (!cursor.moveToFirst()) {
                cursor.close();
                return null;
            }
            return cursor;
        }

Answer 2

Try this Query in your code:

SELECT * FROM TABLE_NAME WHERE Column_name MATCH 'apple*';

Check this link: http://www.sqlite.org/fts3.html#section_3

The FTS engine provides that information via the offsets virtual function. Try this

SELECT offsets(docs) FROM docs WHERE docs MATCH 'apple*';

Enter this in your code:

 Cursor cursor = database.rawQuery("SELECT column_name, offsets(Tablename) FROM entries WHERE column_name MATCH '"apple *"'",null);

Then use cursor.