Substring For Sqlite Android

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

Solution 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.

privateArrayList<String> getQueryStrings(String[] queryWords) {

    privateArrayList<String> queryStringList = newArrayList<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.