Ale1794 Ale1794 - 5 months ago 9
SQL Question

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

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

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