WHERE IN clause in Android sqlite?

Question

I can't get WHERE IN clause to work on android SQLite database.

Is there any way to execute a statement like this in android? :

SELECT body FROM table1 WHERE title IN ('title1', 'title2', 'title3')

What exception are you getting? What is does your code look like? — Maximus, Jun 06 '11 at 22:46

score 16 · Answer 1 · answered Mar 27 '15 at 21:22

You can use TextUtils.join(",", parameters) to take advantage of sqlite binding parameters, where parameters is a list with "?" placeholders and the result string is something like "?,?,..,?".

Here is a little example:

Set<Integer> positionsSet = membersListCursorAdapter.getCurrentCheckedPosition();
List<String> ids = new ArrayList<>();
List<String> parameters = new ArrayList<>();
for (Integer position : positionsSet) {
    ids.add(String.valueOf(membersListCursorAdapter.getItemId(position)));
    parameters.add("?");
}
getActivity().getContentResolver().delete(
    SharedUserTable.CONTENT_URI,
    SharedUserTable._ID + " in (" + TextUtils.join(",", parameters) + ")",
    ids.toArray(new String[ids.size()])
);

Thanks. This helped me a lot. I didn't know I had to supply a separate placeholder ('?') for each parameter in the "IN" clause. I thought a single placeholder with a single String parameter (multiple values joined in it) was enough. — stan0, Mar 08 '17 at 14:12
is this how you did it? String where = ContactsContract.RawContacts.CONTACT_ID + " IN (? ?)"; — Oladipo Olasemo, May 17 '17 at 12:59

score 11 · Answer 2 · answered Jun 06 '11 at 22:49

11

You will have to use the rawQuery method:

Cursor c = db.rawQuery("SELECT body FROM table1 WHERE title IN ('title1', 'title2', 'title3')");

answered Jun 06 '11 at 22:49

Cristian

198,401
62
356
264

Thanks! It worked. I was trying to it this way before, but apparently I had some syntax issues... – fediva Jun 07 '11 at 01:13
1

As rawQuery is prone to SQL injection if we pass dynamic parameters, can you suggest to get similar results without rawQuery ? – Gem Jan 29 '19 at 06:03

score 2 · Answer 3 · answered Jun 06 '11 at 22:53

2

If you are using a content provider you can use the query function as such:

getContentResolver().query(URI,new String[] {"body"}, "title IN ?", new String[] {"('title1','title2','title3')"}, null)

Note that I have not tested this, but according to the documentation, this should work.

answered Jun 06 '11 at 22:53

Dimse

1,476
1
10
15

this doesn't work in Android 4.0.4 - SQLite wrapper classes throw exceptions – Anatoliy Oct 15 '12 at 07:42
2

This won't work. It will get translated into `SELECT body FROM table1 WHERE title IN '('title1', 'title2', 'title3')'`. Notice `'`s around the whole argument part. – Antimonit Sep 15 '15 at 20:09

score 1 · Answer 4 · edited Oct 14 '14 at 05:56

1

Another way of doing this

Cursor mCursor = SQLiteDatabase.query(true, "Name of table","String array of selection columns","title  in ('title1', 'title2', 'title3')", null, null, null, null, null);
if (mCursor != null) {
    mCursor.moveToFirst();
}

edited Oct 14 '14 at 05:56

Pratik Butani

60,504
58
273
437

answered Jul 06 '12 at 06:53

Zain Ali

15,535
14
95
108

score 1 · Answer 5 · edited May 23 '17 at 12:00

1

Please, take a look at this answer. It helped me in the similiar case.

String[] names = { "name1", "name2" }; // do whatever is needed first
String query = "SELECT * FROM table" + " WHERE name IN (" + makePlaceholders(names.length) + ")";
Cursor cursor = mDb.rawQuery(query, names);

The main point here is to provide variable question-mark placeholders (?) to match your IN clause items. And of course - argument for them as String array.

edited May 23 '17 at 12:00

Community

1
1

answered Oct 15 '12 at 07:48

Anatoliy

500
3
17

do you know how to do this with an array of INT[]? My column onto which the IN applies is of type INTEGER. – Brabbeldas May 15 '15 at 13:02
you have to convert your arbitrary array to String[] – Anatoliy May 27 '15 at 17:24

score 1 · Answer 6 · answered Mar 24 '18 at 04:23

1

You need to write rawquery that way -

 Cursor cursor = db.rawQuery("SELECT colunm1 FROM table WHERE colunm2 IN ('value1', 'value2', 'value3')", null);

answered Mar 24 '18 at 04:23

Kamal Bunkar

1,354
1
16
20

score 0 · Answer 7 · answered Jul 28 '15 at 21:37

Adding to Cristian answer: While his answer will work, you should try to use the correct way of querying:

db.rawQuery("Select * from table1 where title = ?", new String[]{param1});

This approach can also be used with WHERE IN clause, but the passed string has to be in format of 'asd','zxc','qwe'

Like this:

String param1 = "\'asd\',\'zxc\',\'qwe\'";
db.rawQuery("Select * from table1 where title in (?)", new String[]{param1});

WHERE IN clause in Android sqlite?

7 Answers7

Linked