Android autocomplete with SQLite LIKE works partially

Question

I have a Restaurants table in my SQLite DB that have the following records

| Name   | Latin_Name |
+--------+------------+
| Манаки | Manaki     |
+--------+------------+
| Енрико | Enriko     |
+---------------------+

Now I'm doing the search like this:

From my fragment:

String selection = DBHelper.COL_NAME + " LIKE ? OR " +
                    DBHelper.COL_LATIN_NAME + " LIKE ?";

String[] selectionArgs = {"%"+term+"%", "%"+term+"%"};

CursorLoader loader = new CursorLoader(getActivity(), 
                               DatabaseContentProvider.REST_CONTENT_URI, 
                               columns, selection, selectionArgs, null);

The content provider query method:

 public Cursor query(Uri uri, String[] projection, String selection,
                    String[] selectionArgs, String sortOrder) {

    db = dbHelper.getReadableDatabase();
    SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
    switch(URI_MATCHER.match(uri)){
        case REST_LIST:
            builder.setTables(DBHelper.REST_TABLE);
            break;
        case REST_ID:
            builder.appendWhere(DBHelper.COL_ID + " = "
                    + uri.getLastPathSegment());
            break;
        default:
            throw new IllegalArgumentException("Unsupported URI: " + uri);
    }
    Cursor cursor = builder.query(db, projection, selection, selectionArgs,
            null, null, sortOrder);


    cursor.setNotificationUri(getContext().getContentResolver(), uri);

    return cursor;
}

So pretty basic right? Now here comes the problem:

If the term that comes in is en I can see the Enriko restaurants among the others.

If I pass enri I can't see the Enriko as a result anymore.

Same goes for the Manaki restaurant I can see it until mana and after that (for manak term for ex) I can't see it in the results list.

I was debugging my ContentProvider and I realized that the cursor was empty, so the problem have to be at the database level, I guess.

Please help.

UPDATE:

Having the @laalto comments in mind I decided to do some test on the database. In the onCreate() method of my SQLiteOpenHelperI inserted only those two records in the table by hand and it worked.

Now the problem is that I have to insert 1300 records onCreate() from a json file shipped in the assets folder. Right now I'm parsing the json file create an ArrayList<Restaurant> then loop trough it and insert one record per object for all 1300 items.

That kind of insertion won't work with the SQLite LIKE method.

Are there any gotchas about this kind of populating the database?

Maybe I need to change file encoding (it is UTF-8 now) or maybe database's collation, or maybe getString() from JSONObject can be tweaked for the database?

Answer 1

If it's urgent, here a quick and dirty solution.

2.600 strings (1.300 cyrillic + 1.300 latin) are really few values to handle in memory: you can simply load all values from the SQLite with

select name, latin_name from restaurants

and then you can loop the result set in Java while searching for the matching string with a String.contains method. Just remember that contains method is case sensitive, so you should use something like this:

string1.toLowerCase().contains(string2.toLowerCase())

Answer 2

sqlite documentation link

The LIKE operator does a pattern matching comparison. The operand to the right of the LIKE
operator contains the pattern and the left hand operand contains the string to 
match against the pattern. A percent symbol ("%") in the LIKE pattern matches any
sequence of zero or more characters in the string. An underscore ("_") in the LIKE 
pattern matches any single character in the string. Any other character matches 
itself or its lower/upper case equivalent (i.e. case-insensitive matching). 
(A bug: SQLite only understands upper/lower case for ASCII characters by default. 
The LIKE operator is case sensitive by default for unicode characters that are
beyond the ASCII range. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ'   
is FALSE.)