How to Correctly Use SQL’s like in Android

Yesterday I stumbled about the correct usage of the LIKE-statement in conjunction with selectionArgs.

My first attempt was to use it as this:

Cursor contactsContractContacts = 
    ContactsContract.Contacts.CONTENT_URI, projection, 
    ContactsContract.Contacts.DISPLAY_NAME + " like '%?%'" ,
    new String[]{filterStr}, 
    ContactsContract.Contacts.DISPLAY_NAME + " ASC");

Now this only led to this nice message in the log:

android.database.sqlite.SQLiteException: bind or column index out of range: handle 0x13208a0

Hm. Maybe I should just drop the dashs?

But this also didn’t work. The exception though has changed:

android.database.sqlite.SQLiteException: near "%": 
syntax error: , while compiling: 
SELECT ... FROM ... 
WHERE ((display_name like %?%)) 
ORDER BY display_name ASC

After searching around for an explanation I’ve finally found the solution on stackoverflow. Stackoverflow is a question and answer-platform for developers and has a very active Android-community. In this case SO-user ernazm and SO-user dalandroid described the solution which I adopted to my needs:

Cursor contactsContractContacts = resolver.query(
    ContactsContract.Contacts.CONTENT_URI, projection,
    ContactsContract.Contacts.DISPLAY_NAME + " like ?",
    new String[]{"%" + filterStr + "%"},
    ContactsContract.Contacts.DISPLAY_NAME + " ASC");

This finally worked! There is also an issue for this on Android’s issue tracker, opened in 2009!

Things like that cost so much time – and can be pretty annoying. Gladly communities like stackoverflow exist to find solutions for such problems. Any stumbling block you want to comment about? What drove you to stackoverflow or Google for solutions?

Share this article:

You can leave a response, or trackback from your own site.

5 Responses to “How to Correctly Use SQL’s like in Android”

  1. Coding Crow says:

    Very useful treatment of quite uncommon topic.

  2. Just added my answer to the SO post for those working with `getContentResolver().query` here how I managed it:

    public static String SELECTION_LIKE_EMP_NAME = Columns.EMPLOYEE_NAME
    + ” like ‘%?%'”;

    String selection = SELECTION_LIKE_EMP_NAME.replace(“?”, sensorId);

    Cursor c = context.getContentResolver().query(contentUri,
    PROJECTION, selection, null, null);

    • This solution doesn’t help. You use selectionArgs to be on the safe side against malicious users trying to do nasty stuff with your database by using SQL injections.

      So you have to use selectionArgs. And for that only "%" + filterStr + "%" works.

      BTW: Thanks to your comment I missed some crucial parts missing in my code snippet above.

  3. Vikrant says:

    Thanx a lot. I was searching for this for a long time. I am gonna bookmark this so that I can share the link with other developers. :)

Leave a Reply

You can also subscribe without commenting.

Subscribe to RSS Feed My G+-Profile Follow me on Twitter!