stanna stanna - 3 months ago 9
Android Question

How to do a SQLiteDatabase contains search in Android

As far as I'm aware of the character

?
is used as a placeholder and it will be replaced with the value that you pass in the
whereArgs
String
array.

This is my code for the query:

String whereClause = null;
String[] whereArgs = null;
if (search != "") {
whereClause = InventoryEntry.COLUMN_INVENTORY_ITEM_NAME +
" CONTAINS(" + InventoryEntry.COLUMN_INVENTORY_ITEM_NAME + ",?)";
whereArgs = new String[]{search};
}

Cursor cursor = db.query(InventoryEntry.TABLE_NAME,
null,
whereClause,
whereArgs,
null,
null,
orderBy
);


When I go to execute the query I get the following error:

android.database.sqlite.SQLiteException: near "CONTAINS": syntax error (code 1): , while compiling: SELECT * FROM items WHERE itemName CONTAINS(itemName,?) ORDER BY date ASC


What do I need to change so that my query will be in the form of:

SELECT * FROM items WHERE itemName CONTAINS(itemName,`search`);


Also, is it proper practice to use
CONTAINS
over
LIKE
?

Thank you!

Answer

SQLite does not have a CONTAINS keyword.

You could do something LIKE this:

whereClause = InventoryEntry.COLUMN_INVENTORY_ITEM_NAME +
        " LIKE " + InventoryEntry.COLUMN_INVENTORY_ITEM_NAME +
        " OR " + InventoryEntry.COLUMN_INVENTORY_ITEM_NAME +
        " LIKE ?";
whereArgs = new String[]{search};

Also, do not compare strings with the != operator, use the equals() method::

if (!"".equals(search)) {
    // ...
}

Or check the length of the String:

if (search != null && search.length() > 0) {
    // ...
}

The != operator checks for reference equality when comparing objects.