SQLite upper and lower case accentuation with LIKE statement

Question

I've this 4 contacts stored in my android contacts list, it's a SQLite database.

António Meireles
AntÓnio Pinto
Ágata Silva
ágata Pereira

If I execute:

select _id, display_name from raw_contacts where upper(display_name) like upper('%antó%')

I get António Meireles and not both Antónios.

If I execute:

select _id, display_name from raw_contacts where upper(display_name) like upper('%á%')

I get ágata Pereira and not both Ágatas.

What is wrong here? Shouldn't the upper() function make it exactly the same on both sides and return both Antónios and Ágatas?

This is happening to me when I try to make my search case insensitive when accents come into equation. From what I read around it is advised to put both (comparator and string to be compared with) either uppercase or lowercase and both strings would match, but that's not happening in my case.

If you can influence the database schema and data going into it, see http://stackoverflow.com/questions/16282083/how-to-ignore-accent-in-sqlite-query-android/16283863#16283863 — laalto, May 26 '14 at 12:52
@laalto I've seen that example before but unfortunately I can't apply it to my case as I'm dealing directly with Android's contact storage database. It's not my own database. — dazito, May 26 '14 at 12:54
Yes I suspected that would be the case and didn't flag as duplicate. I don't know an answer off the bat and don't have the time to experiment right now. — laalto, May 26 '14 at 12:56

Albert Sadowski · Accepted Answer · 2014-05-26T13:27:53.553

According to the SQLite documentation, about LIKE clause:

Any other character matches itself or its lower/upper case equivalent (i.e. case-insensitive matching).

But there is a bug annotation out there:

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

So probably you have to omit the upper clauses and handle upper case for non ASCII characters by adding or case in the where clause.

i.e.

select _id, display_name from raw_contacts 
where 
    ((display_name like '%antó%') or
    (display_name like '%antÓ%'))

SQLite upper and lower case accentuation with LIKE statement

1 Answers1