CODEWITHSUNDEEP
javasqliteunicode
Deepak Bhatia
Deepak Bhatia

Reputation: 6276

Convert Latin Unicode characters stored in database to their English alphabet equivalents

I am creating a search utility that will search elements in a SQLITE DB, Only problem is, that the DB contains some characters like Å è ô which are in Latin...

Is there an easy way to ignore these letters and treat them as there English alphabet counterparts (Å = A, è = e ...) ?

I thought of designing 1 to 1 mappings of all such characters something like,

    HashMap<Character, Character> lstOfChar = new HashMap<Character, Character>();
    lstOfChar.put('Å', 'A');
    lstOfChar.put('è', 'e');

And when retrieving data from database each of such character will be replaced by there English alphabet equivalent and search result will be displayed.

If I am searching Deepak then the rows containing DeepÅk or DÈepak or deepÃk should be searched

But it will be long process and maintenance will be hard too.

Is there some elegant way, may be SQLITE provide some functionality or is it possible through SQL.
I am using Java platform.

EDIT I found the Normalizer in posted answer do help to do the thing programmatic after fetching the results but can this be done by the database or through firing SQL in some special way, as it takes lot of time to fetch the results and apply this function and return result.

Upvotes: 3

Views: 1072

Answers (1)

asthasr
asthasr

Reputation: 9417

You're looking for Normalizer. It allows you to map between accented Unicode characters and their decompositions:

Normalizer.normalize(text, Normalizer.Form.NFD).replaceAll(
    "\\p{InCombiningDiacriticalMarks}+", "");

I'd recommend you do this beforehand, since it is expensive, and chuck it in a "name_search" column or something similar. It's worth noting that this will not capture all non-"English" letters, because they don't always have decompositions, but the basics like é and  will be matched.

Regarding your edit: as I said, the best way to do this is to apply the transformation once, to all rows, and put the result in an additional database column called "name_search" or something similar. It's similar to creating an index on your table. There is no equivalent to the Normalizer's functionality in SQL, and while some RDBMS system might offer one, SQLite certainly does not.

Upvotes: 5

Related Questions