1

I have the following problem: I try to query from the database the names that contain characters with diacritics.

The selection is: 

String like = "ë";
String selection = "LOWER(name) LIKE \"%" + like + "%\"";

The problem is even if i have rows that contain "Ë" the query is empty. Any ideas? And I do not want to build a separate column for normalized names.

john
  • 59
  • 10
  • Since proper unicode collation sequences are not available, the best solution I know is the "separate column for normalized names" you didn't want. http://stackoverflow.com/questions/16282083/how-to-ignore-accent-in-sqlite-query-android/16283863#16283863 – laalto Oct 21 '13 at 07:49
  • Well the problem is that the names are from the contact database and therefore i have to build another local database that always syncs with the contact database on the phone and there normalize the names and therefore adding a lot of complexity and size to my application. That is why i hoped there is a clean solution. But if there is no clean build-in solution probably i will have to stick with your idea. – john Oct 21 '13 at 07:53

2 Answers2

2

From SQLite DOC :-

(A bug: 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, you should replace diacritics before using with like. Remove diacritics from string in Java is good SO to do that. Or to use lower and either cases separate.

Community
  • 1
  • 1
Pankaj Kumar
  • 78,055
  • 25
  • 161
  • 180
  • Nope, not working. The selection is OK, likes like "erwin" "john" work it is not working only when i have diacritics. I have a name Ënt and when like is "Ënt" it does not work. – john Oct 21 '13 at 07:47
  • weird i removed the "LOWER" clause and when i enter Ë now i get the result. The problem is now if i enter ë lowercase it is not working – john Oct 21 '13 at 07:51
  • yep that's true i read that before that's why i tried to use "LOWER" before like. But i see that "LOWER" is not working. – john Oct 21 '13 at 07:55
  • The default built-in lower() function works for ASCII characters only. To do case conversions on non-ASCII characters, load the ICU extension. – Pankaj Kumar Oct 21 '13 at 07:58
  • Aaa, got it. But this is a built-in database for contacts. That means that i cannot load the ICU extension? – john Oct 21 '13 at 07:59
  • Thanks that settles it, using upper and lower separate. – john Oct 21 '13 at 08:02
-1

try this

String selection = "LOWER(name) LIKE \"'%" + like + "%'\"";

Sino
  • 846
  • 7
  • 20