Ignore punctuation in query in Sqlite

Question

I'm using Sqlite with Android (Java). I have a database that contains texts with hebrew punctuation.

My problem is that when I'm doing a SELECT for certain value (without punctuation) I don't get all the results as I guess the DB is not ignoring the records that are punctuated and treating the punctuation as a normal characters.

After doing a search, I found some answers which says I should register a collation for it (sqlite3_create_collation).

As I've never used collations, I would like if some one will give me a hint on how to register it and use it to get the correct full result as I want.

For example:

SELECT * FROM sometable WHERE punctuated_field LIKE '%re%'

I would like to get both the following:

dream
drém

Currently I'm getting just:

dream

I read this relevant answer but didn't managed to understand how to implement it within my query or the Java code.

I would be happy to have someone writing the full query required for me to write within my code.

Thanks in advance!

Answer 1

The Android API does not allow registering custom collations.

You have to make do with the built-in collations, or with Android's LOCALIZED and UNICODE collations.

Answer 2

Since the Android sqlite API doesn't expose anything to set up custom collations, you'll have to figure some other way to solve the problem.

One is to add another column where you have the strings normalized i.e. accent marks ("punctuation" as you like) removed. Then do your LIKE matching on this normalized column and use the original column for display purposes. The cost of this is larger data size and some extra code when inserting into the database.

I've described one such normalization approach in here: How to ignore accent in SQLite query (Android) - I have no idea how well that works with Hebrew chars though.