SQLite Forum

How to define search query for special characters e.g(ā č ō)

How to define search query for special characters e.g(ā č ō)

(1) By ِRazmehr (Razmehr) on 2021-07-12 01:21:20 [link] [source]

'm very new to Sqlite and programing in general so forgive me in advance if my question is not smart. I'm trying to create a dictionary (Pahlavi language to English) in Android studio. When I run the app it works almost perfectly, the only issue is that I'm not able to search for some of Pahlavi alphabet e.g (ā, č, ē) it only works with the standard English alphabet. What should I change in raw query so that my app would be able to search for non English characters as well? Could you please be so kind to help me with this issue?

My sqlite consists out of 3 tables.I created these two tables: words and history. the history table will keep track of the searched words by user and shows them on screen. The Word table has 6 columns: id (integer), pah_word (text), eng_definition (text), synonyms (text), antonyms (text) and examples (text). the pah_word contains Phalvai words with special characters. this is my search query: public Cursor getMeaning(String text) { Cursor c= myDataBase.rawQuery("SELECT eng_definition,example,synonyms,antonyms FROM words WHERE pah_word = ('"+text+"')",null); return c; }

public Cursor getSuggestions(String text) { Cursor c= myDataBase.rawQuery("SELECT _id, pah_word FROM words WHERE pah_word LIKE '"+text+"%' LIMIT 40",null); return c; }

public void insertHistory(String text) { myDataBase.execSQL("INSERT INTO history(word) VALUES(('"+text+"'))");


public Cursor getHistory() { Cursor c= myDataBase.rawQuery("select distinct word, en_definition from history h join words w on h.word==w.pah_word order by h._id desc",null); return c; }

public void deleteHistory() { myDataBase.execSQL("DELETE FROM history"); }`

(2.1) By Warren Young (wyoung) on 2021-07-12 13:24:51 edited from 2.0 in reply to 1 [source]

I'm very new to Sqlite and programing in general

Everyone started there, but this is not the place to learn how to program. We will expect a certain basic level of competence with your tools.

forgive me in advance if my question is not smart

How To Ask Questions The Smart Way

Pahlavi alphabet e.g (ā, č, ē)

That looks like a transliteration into a Western alphabet, not Pahlavi script. Your problems may multiply once you start adding right-to-left and ligatures and all the other stuff you get in non-Western scripts.

What should I change in raw query

First, stop using raw queries. It's unsafe to concatenate user-provided text with static SQL text — as with your "+text+" code — and then execute it.

Instead, you should prepare your SQL in advance, which the Android SDK calls "compiling" for some reason. (By using standard SQLite terminology, you can also get value out of SQLite's C API documentation.) Once you have a prepared statement object, you can then bind values to the indexed parameter spots in the prepared query. This prevents SQL injection attacks, among other benefits.

search for non English characters

SQLite isn't built with ICU by default, but since I'm not an Android developer, I cannot tell you whether Google builds their SQLite library with ICU enabled. If I may guess from the existence of projects like this one, they do not. Without that extension or something like it, LIKE is documented to do surprising things with non-ASCII Unicode characters. The ICU build option not only allows Unicode-aware case folding, it also allows for collation to work properly and more.

While I was researching this answer, I found — right up at the top of the documentation for Android's SQLiteDatabase class — a note about Unicode collation, which you may find valuable.