Substring for SQLite Android

Question

I'm trying to do a particular function for my code. Suppose, in my database, there is an entry called tandoori chicken. How do I code the SQL part so that I can filter the database with chicken tandoori and not just fixed on tandoori chicken?

public class MyDatabase extends SQLiteAssetHelper {

    private static final String DATABASE_NAME = "FoodDatabase1.sqlite";
    private static final int DATABASE_VERSION = 1;

    public MyDatabase(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

And the getFood function.

/*
* Receive searchQuery in string form
* return Cursor object
*/
public Cursor getFood(String searchQuery) {

    SQLiteDatabase db = getReadableDatabase();
    SQLiteQueryBuilder qb = new SQLiteQueryBuilder();

    String [] sqlSelect = {"_id", "FOOD", "CALORIES"};
    String sqlTables = "fooddb1";

    String whereClause=null;
    String[] whereArgs=null;

    /*
    *if searchQuery is empty then null will be passed in query() function which 
    *will display all rows
    *if searchQuery is not null it will look for match in the table
    */

    if(!searchQuery.equals("")){
        whereClause="Food LIKE ?";

        /*
        *LIKE statement will look for substring in the table if found it will add that row to cursor
        */
        whereArgs= new String[] {
                "%"+searchQuery+"%"
        };
    }

    qb.setTables(sqlTables);

    Cursor c = qb.query(db, sqlSelect, whereClause, whereArgs,
            null, null, null);

    c.moveToFirst();
    return c;
}

Answer 1

You just need to split the substrings by space. For example, you've tandoori chicken as search query string. So, now you need to split the query string by space to make two separate words- tandoori and chicken.

Then the sql query should look like

Select * from foodTable where Food like 'tandoori chicken' or 'chicken tandoori'

To achieve this you might consider doing something like this.

String[] queryWords = searchQuery.split(" ");  // Split by space

Now make the words and put them in an ArrayList.

private ArrayList<String> getQueryStrings(String[] queryWords) {

    private ArrayList<String> queryStringList = new ArrayList<String>();
    // Now let us do some combination of words here and add each combination in the ArrayList.
    for(int i = 0; i < possibleCombinationCount; i++)
        queryStringList.add(getWordsCombination(i)); 

    return queryStringList; 
}

Now make the query string as you like.

String builder = "";

for(String wordsComb : getQueryStrings()) {
    // make the query with or 
    if(builder.length != 0) builder += " or ";
    builder += "'%" + wordsComb + "%'";
}

String query = "Select * from foodTable where Food like " + builder;

Now run the rawQuery() on your database.

db.rawQuery(query);

This solution may work well for two or three words in a string while it won't work well for long strings.

If your search is flexible like you just want to find the rows matched with the given strings you might consider using the IN statement.

Select * from foodTable where Food in (queryWords[0], queryWords[1], ....)

Just you need to build the database query of your own with the values separated from the query string by space in queryWords array.

I found this answer relevant to your question too.