sql - instr() function SQLITE for Android?

Question

I wanna do the following on my existing sqlite database on android which is kind a built like that colomns: id --- rule --- path --- someotherdata

A rule now e.g. contains a part of a filename (either just some trivial stuff like "mypicture" or also a filetype like "jpg"). Now what I want to do is, I want to write a query, which gets my all rules, which contain a part of an inputstring. I have tried following example: String value = "somepicturefilename.jpg"

my statement:

"SELECT DISTINCT * FROM " + TABLE_RULES + " WHERE instr('"+ value + "',rule)!=0 ORDER BY " + KEY_ID+ " DESC;"

the statement is in java so the "value" should get inserted in the statement.

However, this does not work. I am not too familiar with sql nor sqlite, does anyone have a tip= ;) thanks.

edit: i've also tried charindex, which didn't work either.

edit: so a more detailed example. following database is given.

id --- rule ---
1      "jpg"
2     "ponies"
3     "gif"
4     "pdf"  

Now the user enters a filename. Let's say "poniesAsUnicorns.jpg". So "poniesAsUnicorns.jpg" is my input string and the query should match both id#1 and id#2 because "poniesAsUnicorns.jpg" contains both "jpg" and "ponies"

I hope that clarifies what I want.

edit: here is what i tried too:

String statement = "SELECT DISTINCT * FROM " + TABLE_RULES
            + " WHERE charindex(rule,'" + value
            + "') > 0 ORDER BY " + KEY_ID + " DESC;";

but throws a "no such operation" exception.

Answer 1

AFAIK instr() is not available, so you can use:

select * from table where replace("poniesAsUnicorns.jpg", rule, "") != "poniesAsUnicorns.jpg"; 

or for case insensitive matches:

select * from table where replace(upper("poniesAsUnicorns.jpg"), upper(rule), "") != upper("poniesAsUnicorns.jpg");