Android: ContactsContract query, need more advanced selection string

Question

i'm trying to query the ContactContract ContentProvider and obtain the data that the following algorithm would obtain:

given a phone number (input), return record...:
if(recordNumber has 7 digits) {
    if('%recordNumber' LIKE 'inputNumber') {
        return recordDisplayName;
    }
} else if(recordNumber has 10 digits) {
    if('recordNumber' LIKE '%inputNumber') {
        return recordDisplayName;
    }
} else if(recordNumber == inputNumber) {
    return recordDisplayName;
}

this works in the query call:

ContactsContract.CommonDataKinds.Phone.NUMBER+" LIKE ?"

but i need something more like this:

"('%" + ContactsContract.CommonDataKinds.Phone.NUMBER+"' LIKE '?' AND LENGTH("+ContactsContract.CommonDataKinds.Phone.NUMBER+")=7) OR ('" + ContactsContract.CommonDataKinds.Phone.NUMBER+"' LIKE '%?' AND LENGTH("+ContactsContract.CommonDataKinds.Phone.NUMBER+")=10)"

but i get runtime errors everytime i use a query with a single quote. for instance, changing:

ContactsContract.CommonDataKinds.Phone.NUMBER+" LIKE ?"

to:

ContactsContract.CommonDataKinds.Phone.NUMBER+" LIKE '?'"

causes a runtime error "bind or column index out of range...". so this has to be some syntax error... right? what's the proper syntax for ContentProvider queries and/or how can i get the result set i'm after from ContactsContract?

Answer 1

queries to ContentProviders are a little different, and unforgiving on the formatting. for ContentProviders, if you want to use '%' as a wildcard character, the '%' must be concatenated to the argument in the WHERE clause, rather than as a part of the clause itself.

correct:

Cursor cursor = getContentResolver().query(
    ContactsContract.Data.CONTENT_URI, 
    null, 
    ContactsContract.CommonDataKinds.Phone.NUMBER+" LIKE ?",
    new String[] { "%"+number },
    null);

incorrect:

Cursor cursor = getContentResolver().query(
    ContactsContract.Data.CONTENT_URI, 
    null, 
    ContactsContract.CommonDataKinds.Phone.NUMBER+" LIKE %?", 
    new String[] { number }, 
    null);

the above "incorrect" statement is actually completely legal for SQLite queries, just not for ContentProvider queries.

in addition, single quotes are a syntax error in a ContentProvider query's WHERE clause (or concatenated to the args in said clause).

final code:

if(number.length()==7) {
    cursor = getContentResolver().query(ContactsContract.Data.CONTENT_URI, null, 
        ContactsContract.CommonDataKinds.Phone.NUMBER+" LIKE ?",
        //"('%" + ContactsContract.CommonDataKinds.Phone.NUMBER+"' LIKE '?' AND LENGTH("+ContactsContract.CommonDataKinds.Phone.NUMBER+")=7) OR ('" + ContactsContract.CommonDataKinds.Phone.NUMBER+"' LIKE '%?' AND LENGTH("+ContactsContract.CommonDataKinds.Phone.NUMBER+")=10)",
        new String[] { "%"+number },
        null);
} else if(number.length()==10) {
    cursor = getContentResolver().query(ContactsContract.Data.CONTENT_URI, null, 
        "("+ContactsContract.CommonDataKinds.Phone.NUMBER+"=? AND LENGTH("+ContactsContract.CommonDataKinds.Phone.NUMBER+")=7) OR ("+ContactsContract.CommonDataKinds.Phone.NUMBER+"=? AND LENGTH("+ContactsContract.CommonDataKinds.Phone.NUMBER+")=10)",
        new String[] { number.substring(3), number },
        null);
} else {
    cursor = getContentResolver().query(ContactsContract.Data.CONTENT_URI, null, 
        ContactsContract.CommonDataKinds.Phone.NUMBER+"=?",
        new String[] { number },
        null);
}

this code will retrieve matching display names for phone numbers that match the number in question.
- if the number is a 7 digit number, we retrieve any records who's phone numbers last 7 digits (dashes and special characters stripped, except "*" and "#" since they are valid phone characters) match the 7 digits of the number in question.
- if the number is a 10 digit number, it returns any records that either contain 7 digits and match the last 7 characters, or contain an exact match in the case that the record has 10 digits.
- if the number in question has neither 7 nor 10 digits, an exact match is necessary.