Execute Query in android searching if a string is contained inside the string inserted in a column

Question

I have a database in an android app and the following are its columns:

public class DBhelper extends SQLiteOpenHelper {
    public static final String COL_TIPO = "ColonnaTipo";
    public static final String COL_DIFFICOLTA="ColonnaDifficolta";
    public static final String COL_INGREDIENTI = "ColonnaIngredienti";

    //code
}

I use this function to make a query on it (TAB is the name of the table):

public Cursor query_filtri(String[] param, String[] p2) {
    String whereClause="";
    String[] whereArgs=new String[50];
    whereClause=COL_TIPO+"=? AND "+COL_DIFFICOLTA+"=?";
    whereArgs=new String[] {param[0],param[1]};

    return  getWritableDatabase().query(TAB,null,whereClause,whereArgs,null,null,null);
}

In param[] there are the values of COL_TIPO and COL_DIFFICOLTA that i am searching, and it works.

In p2[] there are 2 strings: p2[0] = "hi", p2[1]= "try". I would modify the query to obtain the colums that in COL_INGREDIENTI contains the worlds hi and try.

For example:

I would try to obtain this

COL_INGREDIENTI "hi, would you try?"

and not this

COL_INGREDIENTI "hi, how are you?"

How can i modify the query? Is there a way to make it not case-sensitive?

Answer 1

You can add:

AND COL_INGREDIENTI like '%hi%' and COL_INGREDIENTI like '%try%'

From the documentation:

The LIKE operator is case sensitive by default for unicode characters that are beyond the ASCII range. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE

So, if you are using characters inside de ASCII range you don't need to worry about the case