0

To be more specific, i'm writing a dictionary-like app for android and would like to query words with umlaut characters (ä,ü,ö) in them without having the user type them out. for example the query should return "aufhören" as a result if the user searches for "aufhoren".

My solution

The best solution i could think of was replacing possible umlaut characters with '_' before the query but that would introduce the following problem: if the user were to type something like "schatzen", the query would also contain the similar but irrelevant word "schützen" instead of just "schätzen". i would also like to replace s with ß but since it's not a vowel, it won't produce a false query.

Other solutions i've found

  • using replace: i don't really know how heavy the calculations are going to be. there will be 1000+ rows and the query is not limited to the "word" column but also covers the "meaning" and "example" columns (if the user chooses to. it's useful because some words may be used in the example of another word)
  • creating a normalized column : same reason as above, creating one wouldn't help and creating 3 will be doubling the table.

  • collate localized : did not work. according to this question it doesn't work with like '%term%' which kinda makes it useless.

    String query = "select * from worterbuch where wort like '%" + constraint.toString() + "%' order by wort collate localized";
db.setLocale(Locale.GERMANY);
res = db.rawQuery(query,null);
Community
  • 1
  • 1
M47
  • 400
  • 2
  • 13
  • did you know? to get an umlauted vowel, simply keep pressed a, o or u, and select the umlauted character from the popup. You'll need the Unicode (UTF-8) encoding. Same goes for the sz ligature. – Phantômaxx Sep 10 '16 at 22:00
  • @Rotwang yes i know that but all the dictionaries i've used have this feature so i thought i should add it too. – M47 Sep 10 '16 at 22:04
  • If the user enters a wrong word, why should s/he get a result? anyway, I use LIKE, so I can search `aufh*`, `*ren` or even `*ufh*`, depending on a setting. – Phantômaxx Sep 10 '16 at 22:32
  • Anyway... the solution for German is very simple: just use `ae`, `oe` and `ue` for the umlauted vowels. Use `sz` for ß. – Phantômaxx Sep 10 '16 at 22:38
  • @Rotwang i use like as well but using * or _ would eventually return a result that shouldn't be there, like i've explained in the post. afaik this feature is available on All german dictionaries and i want to know how i can apply it to my app without creating extra columns and without making the user long press a key or type 2 chars instead of 1. – M47 Sep 11 '16 at 07:52
  • Well. you were ready to make them type `_`... which is **counter-intuitive**. While `ae`, `oe` and `ue` is very usual for Germans. Because old typing machines didn't have the umlutes vowels nor ß (by the way, you could use the more common `ss` in place of `sz`). Obviously, `*` will not be typed, it's automatically added in the form of `%`, depending on the setting. – Phantômaxx Sep 11 '16 at 09:48
  • @Rotwang no i meant i was gonna replace possible umlaut characters (ä,ü,ö) with _ programmatically. [after reading sqlite docs again] i'm confused about your * suggestion. i use % and _ instead of * and ?. as far as i can understand they do the same thing (without the GLOB clause) – M47 Sep 11 '16 at 11:47
  • Forget about *. My suggestion is the same as yours: `replace possible umlaut characters (ä,ü,ö) with _` ... why not using the **correct** replacements? You can store M**ue**ller instead of M**ü**ller (as an example) in your db. And Stra**ss**e instead of Stra**ß**e. Just to name a couple. – Phantômaxx Sep 11 '16 at 11:57
  • Anyway, this seems to be a non-problem: `setLocale() + COLLATE LOCALIZED` from the answer in the link you posted should work just fine. – Phantômaxx Sep 11 '16 at 12:29
  • @Rotwang ok thanks. also collate localized works with '=' and not with 'like'. half-assed implementation of sqlite ... – M47 Sep 11 '16 at 12:55
  • Maybe you're not taking in account the case-sensitivity of LIKE for non-ASCII characters. http://www.sqlite.org/lang_expr.html#like – Phantômaxx Sep 11 '16 at 13:07

0 Answers0