Grokking Android

Getting Down to the Nitty Gritty of Android Development

How to Correctly Use SQL’s like in Android

By

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 = 
    resolver.query(
    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?

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

He has been interested in Android for quite a while and has been blogging about all kind of topics around Android.

You can find him on Google+ and Twitter.