android - How to SQL compare columns when one has accented chars?

Question

I have two SQLite tables, that I would love to join them on a name column. This column contains accented characters, so I am wondering how can I compare them for join. I would like the accents dropped for the comparison to work.

Answer 1

You can influence the comparison of characters (such as ignoring case, ignoring accents) by using a Collation. SQLLite has only a few built in collations, although you can add your own.

• SqlLite, Data types, Collating Sequences
• SqlLite, Define new Collating Sequence

EDIT: Given that it seems doubtful if Android supports UDFs and computed columns, here's another approach:

• Add another column to your table, normalizedName
• When your app writes out rows to your table, it normalizes name itself, removing accents and performing other changes. It saves the result in normalizedName.
• You use normalizedName in your join.

As the normalization function is now in java, you should have few restrictions in coding it. Several examples for removing accents in java are given here.