15

i have a problem with sqlite database. it seems to not support full persian/arabic characters. when i search some fields based on persian chars, most of the times Sqlite can't recognize those chars.

i insert data into database by copy them from HTML file. so when i type string and search, no result show. but if i copy string and search, it works.

The string from the HTML file is:

"احكام خمس"

The typed string is:

"احکام خمس"

CL.
  • 173,858
  • 17
  • 217
  • 259
hojjat reyhane
  • 648
  • 1
  • 8
  • 25
  • 1
    SQLite doesn't know about languages; it simply compares Unicode characters. Show examples of the strings that you think should match. – CL. Mar 10 '14 at 09:27
  • 1
    i tried select some field in sqlite browser and eclipse. and for some words i get no result. this is my query in SQLite Browser : select part from table where season = 'خمس'; it seems to sqlite not recognize 'خمس'. but when i search طهارت it gives good result. i do the same thing in eclipse and get same results. – hojjat reyhane Mar 10 '14 at 09:55
  • And what is the string in the table that you think should match? – CL. Mar 10 '14 at 12:00
  • 1
    the string is 'خمس' and i search for it. when i copy 'خمس' from table cell it works, but when i type it, no result return. – hojjat reyhane Mar 11 '14 at 11:37
  • The string in your comment is identical with that in the question. Did you copy it from the table cell when writing the comment? – CL. Mar 11 '14 at 11:58
  • no i type it. try 'احکام خمس' and see it works or not !!! – hojjat reyhane Mar 11 '14 at 12:49
  • it seems SQLite has problem with space in persian/arabic query !!! in addition it has problem with ی . i replaced ی and ي and it worked. but i don't know how to fix space. – hojjat reyhane Mar 11 '14 at 13:13
  • What space? There is no space in your query `...where season = 'خمس'`. Please edit the question to show both the string in the table and the string you want to match it with. – CL. Mar 11 '14 at 14:00
  • Please show the actual strings! – CL. Mar 11 '14 at 14:45
  • the HTML one is "احكام خمس" and typed one "احکام خمس" – hojjat reyhane Mar 11 '14 at 14:54

3 Answers3

6

These strings are different.

The HTML string begins with the characters U+0627, U+062D, and U+0643 (Alef, Hah, and Kaf).

The third character of the typed string is not U+0643 but U+06A9 (Keheh).

CL.
  • 173,858
  • 17
  • 217
  • 259
  • oh its so bad!!! i have both typed and copied data in my database, and i don't know how to fix it. when i type, SQLite can't show copied data, and if i copy it can't show typed data !!! – hojjat reyhane Mar 11 '14 at 17:35
  • 2
    ok, i find a solution. in SQLite browser i should to replace for all column arabic chars with persian chars to get good result when i type. update table set column = replace(column, "ي", "ی"); update table set column = replace(column, "ك", "ک"); – hojjat reyhane Mar 11 '14 at 18:45
5

Sqlite is very simple supported persian/arabic.
read this link. sqlite database in persian language

Community
  • 1
  • 1
Amadas
  • 703
  • 1
  • 5
  • 10
  • as i said i searched too much, but i can't solve my problem. in comment above you can see my full problem. – hojjat reyhane Mar 10 '14 at 09:58
  • @hojjatreyhane Sqlite support is UTF-16. I don't know very well UTF-16, but UTF-16 will support Persian and Arabic. Try to it. – Amadas Mar 11 '14 at 00:38
  • @Amadas Actually, SQLite uses UTF-8 by default (but this does not matter because the Android libraries automatically convert from/to the encoding used for Java strings). – CL. Mar 11 '14 at 11:56
4

Try this:

select part from table where season = N'خمس';

If that not help, try using operators like and %.

I'm already using SQLite for more than 2 years in Persian supported projects in production environments and has no problem at all.

Udate:

I'm using SQLite via NHibernate. So myself never generate or write a query. To be honest never noticed how NHibernate send queries to SQLite.

Afshar Mohebi
  • 10,479
  • 17
  • 82
  • 126
  • 1
    in sqlite browser when i put N before string it crashes. i used like, but the result was similar. maybe in eclipse it will work!!! – hojjat reyhane Mar 11 '14 at 11:47