CODEWITHSUNDEEP
javaandroidsqlite
dazito
dazito

Reputation: 7980

SQLite upper and lower case accentuation with LIKE statement

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.

Upvotes: 2

Views: 3341

Answers (2)

jlenfers
jlenfers

Reputation: 489

An useful alternative is replacing those accents chars with "_" in the SQL:

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

Upvotes: 0

Albert Sadowski
Albert Sadowski

Reputation: 642

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Ó%'))

Upvotes: 3

Related Questions