CODEWITHSUNDEEP
javaandroidsqliteandroid-sqliteandroid-cursor
android enthusiast
android enthusiast

Reputation: 2092

Android SQLite query to return rows that contains certain text

I need a sqlite query for my local DB that returns all the rows of a table that contains a certain String in a column. The issue is I only have part of that String. 

ID  COLUMN1   COLUMN2    COLUMN3
1  myString11111  timestamp   some_value
2. myString22222  timestamp   some_value

So in the example above I only know myString which is only part of the content of COLUMN1. Here is what I tried so far:

String myQuery = "select * from " +  MY_TABLE + " where " + COLUMN1 + " LIKE ? " + "' " + myString + " '";

Using this query throws an exception

android.database.sqlite.SQLiteException: near "' foo '": syntax error (code 1): , while compiling: select * from my_table where COLUMN1 LIKE ? ' foo '

Does anyone know is is possible to retrieve rows if only part of the content is known? Thank you in advance!

Upvotes: 1

Views: 1152

Answers (1)

forpas
forpas

Reputation: 164089

Since you use ? place holders for the parameters there is no need to concatenate them. You can pass them in the 2nd argument of the rawQuery() method:

String myQuery = "select * from " +  MY_TABLE + " where " + COLUMN1 + " LIKE '%' || ? || '%'";
Cursor c = db.rawQuery(myQuery, new String[] {myString});

where db is an instance of SQLiteDatabase.
The query will return all the rows that contain the value of the string myString in the column COLUMN1.
You can do the same with the function INSTR() instead of the operator LIKE:

String myQuery = "select * from " +  MY_TABLE + " where instr(" + COLUMN1 + ", ?)";

Upvotes: 1

Related Questions