How to use SQL COLLATE operator with Android CursorLoader

Question

I need to create CursorLoader which will extract all items matching a name and type. What is more name must ignore Locale specific letters (accents). For example, if I provide query with:

item_name LIKE '%cafe%' COLLATE LOCALIZED

then I need then to extract all items, which names contain 'cafe' OR 'café'. I have no idea tho, how to provide COLLATE operator to query built like that (with no plain SQL).

    return CursorLoader(
            context,
            someUri,
            arrayOf(
                    PrompterTable.WORD_C,
                    PrompterTable.WORD_PLAIN_C,
                    PrompterTable.ENTRY_ID_C,
                    PrompterTable.CATEGORY_ID_C,
                    lengthClause,
                    PrompterTable.FAVOURITE_C
            ),
            "item_name LIKE ? COLLATE LOCALIZED AND item_type IN (0, 1)",
            arrayOf(myItemName),
            "L" //alias given to lengthClause before
    )

I did set database locale to spanish, I also tryied few ways to build the WHERE statement:

"item_name LIKE ? COLLATE LOCALIZED AND item_type IN (0, 1)"
"(item_name LIKE ? COLLATE LOCALIZED) AND item_type IN (0, 1)"
"item_name LIKE ? AND item_type IN (0, 1) COLLATE LOCALIZED"
"item_name = ? AND item_type IN (0, 1) COLLATE LOCALIZED"
"item_name = ? COLLATE LOCALIZED AND item_type IN (0, 1)"
"(item_name = ? COLLATE LOCALIZED) AND item_type IN (0, 1)"

But every of them won't work I as need (finds only 'cafe'). Query is computed to something like this:

    SELECT word, item_name, entryID, item_type,  length(word) as L , favourite 
    FROM item_table 
    WHERE (item_name LIKE '%cafe%' COLLATE LOCALIZED AND item_type IN (0, 1)) 
    GROUP BY word ORDER BY L LIMIT 25

I tried to reference this topic: Using COLLATE in Android SQLite - Locales is ignored in LIKE statement

But in my case it won't work even with '=' instead of LIKE

Hey Michal, I have a small suggestion, have you considered to move it to objects? Maybe it's going to be easier to handle the exceptions or cases that you want to consider. — Federico Navarrete, Feb 07 '18 at 13:00
If you mean moving filtering to the code then yeah. But there are 2 problems: legacy code which will be more efficient with this solution (and I was explicitly asked to make it using SQL) and second is that Java Collator is not working good with some letters (for example polish a and ą will not be compared well) so it will be even more coding to do it around :/ — Michał Powłoka, Feb 07 '18 at 13:07

score 0 · Accepted Answer · answered Feb 09 '18 at 07:14

I have found the answer, but it is not what I hoped for. Convenient COLLATE LOCALIZED was removed from Android around Nougat release and is not replaced in any way.

Known workarounds are:

Perform filtering in coding (use Normalizer.normalize or Collator java object)
Add additional column to the filtered table with normalized name (no accents etc.) and compare your users input to this column
Add custom COLLATOR to SQLite database, what sounds cool but there is not Java API to do so. You would have to use Anroid NDK and write it in C++ code.

I found these in hire if you wish to get detailed answears: https://discuss.zetetic.net/t/workaround-for-using-androids-localized-collator-after-3-5-0-update/1515/14

How to use SQL COLLATE operator with Android CursorLoader

1 Answers1