sqlite - cant use like clause in android app

Question

I am working on a database with sqllite in an android app I want to retrieve sm data using a like clause ex:

Cursor c = myDB.query(MY_DATABASE_TABLE, null, " SongName LIKE '%"+"=?"+"%'" , 
           new String[]{match_str}, null, null,"SongHit  DESC");

It should give all SongName starting with match_str but its not working.Why?

Answer 1

This:

" SongName LIKE '%"+"=?"+"%'"

Will end up looking like this when the SQL interpreter sees it:

" SongName LIKE '%=?%'"

And that will match any SongName that contains a literal "=?" and I don't think that's anything like what you want.

A % matches any sequence of characters in an SQL LIKE, it is essentially the same as .* in a regular expression; so, if you want to match at the beginning then you don't want a leading %. Also, your ? placeholder needs to be outside the single quotes or it will be interpreted as a literal question mark rather than a placeholder.

You want something more like this:

String[] a = new String[1];
a[0]       = match_str + '%';
Cursor   c = myDB.rawQuery("SELECT * FROM songs WHERE SongName LIKE ?", a);

If you wanted to be really strict you'd also have to escape % and _ symbols inside match_str as well but that's left as an exercise for the reader.