Tuesday, January 10, 2012

Android Like SQL Statement, sqllite sql software

While using sqllite sql software for android, I was runing into problems. I was able to do exact matches, but for some reason I couldn't get LIKE matches to work. I was hunting around like crazy trying to figure out how to write a LIKE statement in Android. I had a working example of '=' and figured it would be pretty simple to use the LIKE command. After many variations I just couldn't figure it out. I almost posted to stackoverflow but decided to read the information on the "rawQuery" parameters of the database methods.

After doing a little reading it turns out that if you enter in a ? in your query it pulls the selection arguments and stuffs them in where that ? is. My first example didn't exactly make it clear that ? was replaced with the arguments, so now that I knew that it led me to my next train of thought.

As silly as this might sound my original query that pulled exact matches in used this:
cursor = db.query(DBHelper.TABLE, null, DBHelper.C_TEXT + " =?", whereArgs, null, null,null);

I finally tried this:
cursor = db.query(DBHelper.TABLE, null, DBHelper.C_TEXT + " LIKE ?", whereArgs, null, null,null);

Guess what? The whereArgs get stuffed in the ? position.

As much as I could have sworn I tried this, apparently I tried various iterations. Be sure if you're going to  use this, make sure you have "column" space "LIKE" space "?" and then your arguments as the next parameter. If you notice that the first example I was using had no space between the front, between the = and ? as well as behind, this is part of what tripped me up, LIKE is a word so you have to be sure to separate them using the right spaces.

If you're not sure what a column is take a look at W3 Schools SQL Like Tutorial. It doesn't explain the ? and how that works with respect to android. But after reading to this point you should be able to use the LIKE command in android.

I hope this helps others, so long and short of it, I can now query my db with a like operator, and it works.

Note* whereArgs contains only one element which contains % on either side in my case (%string%). Perhaps in the future I'll poke around at the options when I have more then one string in that field.