Sqlite Query for multiple values in one column

Question

I wanted to do query in table for field id with some vales like 1,5,4,11 which will come from previous screen according to selection.

cursor = database.query(tablename,
                    new String[] { "TopName" }, "id =?", new String[]{"2,3"}, null, null, null);

When I do like this, I am getting cursor count 0, with new String[]{"2"} I am getting value I want for all ids with values in string array like OR which have value in that column.

Lalit Poptani · Accepted Answer · 2013-05-21T04:05:47.923

20

You can use the IN operator like this,

cursor = database.query(tablename, new String[] {"TopName"}, "id IN(?,?)", 
                                        new String[]{"2","3"}, null, null, null);

edited May 21 '13 at 04:05

answered Dec 01 '11 at 16:19

Lalit Poptani

67,150
23
161
242

1

what if we want to match with one more column with the same scene? – Abhishek Jul 15 '15 at 11:08

score 17 · Answer 2 · answered Mar 06 '13 at 02:13

The correct syntax for using the IN operator in Android's ContentProvider is as follows:

cursor = database.query(contentUri, projection, "columname IN(?,?)", new String[]{"value1" , "value2"}, sortOrder);

Alternatively, we can also use,

cursor = database.query(contentUri, projection, "columnName IN(?)", new String[] {" 'value1' , 'value2' "}, sortOrder);

Note that we need single quotes around each comma-separated value in the arguments for second case, otherwise the whole string will be treated as one value for the column. The SQL will treat it as

SELECT * FROM table WHERE columnName IN ('value1,value2')

instead of the correct syntax

SELECT * FROM table WHERE columnName IN ('value1' , 'value2')

score 5 · Answer 3 · answered Dec 01 '11 at 16:21

VolkerK was first to correctly answer the question, but for the sake of completeness here is a full example of how to use the IN operator:

cursor = database.query(tablename,
                new String[] { "TopName" }, "id IN (?)", new String[]{"2,3"}, null, null, null);

score 3 · Answer 4 · answered Dec 01 '11 at 15:39

3

Use the IN operator instead of equality comparison (=).

answered Dec 01 '11 at 15:39

VolkerK

95,432
20
163
226

score 1 · Answer 5 · answered Dec 01 '11 at 16:06

1

For the SelectionArgs section I think you need to change:

new String[]{"2,3"}

To

new String[]{"2","3"}

answered Dec 01 '11 at 16:06

Neil Hoff

2,025
4
29
53

That's not what the OP is looking for, and it would actually cause an error since the number of supplied arguments is different from the number of `?` fields. VolkerK's answer is correct. – Graham Borland Dec 01 '11 at 16:08
Oh ok...I see it now. I have the same question as ud_an then. How do you use the "IN" operator in the query above? – Neil Hoff Dec 01 '11 at 16:14

score 0 · Answer 6 · edited May 23 '17 at 12:17

I would like to put this here since a compendium of answers helped me putting multiple (unknown) values in SQLiteDatabase.query() and the one-question-mark did not work for me. Hope helps anyone

// API > 24
protected String attributesAsMarks(String[] attributes) {
    List<String> marks = Collections.nCopies(attributes.length, "?");

    return marks.stream().collect(Collectors.joining(","));
}

// I'm using API > 15
protected String attributesAsMarks(String[] attributes) {
    StringBuilder sb = new StringBuilder();
    String separator = "";

    for (String s : attributes) {
        if (s == null) continue;

        sb.append(separator).append("?");
        separator = ",";
    }

    return sb.toString();
}

Thanks to

Sqlite Query for multiple values in one column

6 Answers6

Linked