Grokking Android

Getting Down to the Nitty Gritty of Android Development

How to Correctly Use SQL’s like in Android

By 9 Comments

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: 
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?

Wolfram Rittmeyer lives in Germany and has been developing with Java for many years.

In recent years he shifted his attention to Android and blogs about anything interesting that came up while developing for Android.

You can find him on Google+ and Twitter.

9 thoughts on “How to Correctly Use SQL’s like in Android”

  1. Very useful treatment of quite uncommon topic.

    1. Thanks! It simply drove me nuts before I’ve found the solution for it – so I blogged about it 🙂

  2. Scott Alexander-Bown

    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);

    1. 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. 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. 🙂

  4. Thank you so much Wolfram! This solved my problem.

    In fact, I just wanted to say that I’m finding your blog very valuable. You’ve helped me out on more than one occasion – your series on content providers was brilliant, you actually explained what was going on and I don’t think I would have really ever understood it without your help! 🙂

  5. i used same your solution for me but it shows error
    Below is my query

    Cursor cc = getContentResolver().query( Constant.PRODUCT_CONTENT_URI, new String[]{ProductItem.KEY_PRODUCT_ID, ProductItem.KEY_NAME},
    ProductItem.KEY_NAME+” LIKE ?”,
    new String[]{“%” + productname + “%”},
    ProductItem.KEY_NAME+” ASC”);

    IT SHOWS me cursor count bt with exception ========

    java.lang.IllegalStateException: Couldn’t read row 0, col -1 from CursorWindow. Make sure the Cursor is initialized correctly before accessing data from it.

    what to do please tell me

    1. Wolfram Rittmeyer

      Probably the interesting part is what you do with the Cursor? Did you move the Cursor before the first position (cc.moveToFirst())?

  6. Thanks! Helped a lot. Couldn’t find much help anywhere else.

Leave a Reply

Your email address will not be published. Required fields are marked *