Instr() Function Sqlite For Android?

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 o

Solution 1:

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

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

or for case insensitive matches:

select*fromtablewhere replace(upper("poniesAsUnicorns.jpg"), upper(rule), "") !=upper("poniesAsUnicorns.jpg");

Solution 2:

"SELECT DISTINCT * FROM " + TABLE_RULES + " WHERE rule LIKE '%"+ value + "%' ORDER BY " + KEY_ID+ " DESC;"

try this one.

Solution 3:

Use underscore(_) in the LIKE pattern instead of percentage(%). Underscore will matches any single character in the value instead of the entire string sequence.

"SELECT DISTINCT * FROM " + TABLE_RULES + " WHERE rule LIKE '_"+ value + "_' ORDER BY " + KEY_ID+ " DESC;"

LIKE operators

Solution 4:

I can't see anything wrong with your SQL expression ... the only thing that might be killing it is the lack of spaces around the inequality operator !=

Try this:

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

I'm not sure if the SQLite implementation in Android is complete, you may find that some ops don't work, so do some simple tests as a sanity check.

If that doesn't work you can reverse the condition.

[field]='some_value'

is just as valid as:

'some_value'=[field]

So in your example I thought you should be able to do something like:

selectdistinct*from TABLE_RULES
where'poniesAsUnicorns.jpg'like'%'+ rule +'%';

I tested this in mySQL and it doesn't like the + concatenation but it does work happily like this:

selectdistinct*from TABLE_RULES
where'poniesAsUnicorns.jpg'like replace('%rule%','rule',rule);

Solution 5:

"SELECT DISTINCT * FROM " + TABLE_RULES + " WHERE '" + value + "' like '%' || rule || '%' ORDER BY " + KEY_ID + " DESC;"