Turkish character in SQLite while using LIKE expression

Question

select *from urunler where musteri like %ir%;

test data:

+---musteri---+---ID--+
+-------------+-------+ 
+---İrem------+---1---+ 
+---Kadir-----+---2---+ 
+---Demir-----+---3---+ 

returning result:

Kadir
Demir 

if use %İr% then İrem is returning but Kadir and Demir not returning. There same problem in other turkish characters, but not any exact solution. I am programming mono android.

---

    [SQLiteFunction(Name = "TOUPPER", Arguments = 1, FuncType = FunctionType.Scalar)]
    public class TOUPPER: SQLiteFunction
    {
        public override object Invoke(object[] args)
        {
            return args[0].ToString().ToUpper();
        }
    }       

    [SQLiteFunction(Name = "COLLATION_CASE_INSENSITIVE", FuncType = FunctionType.Collation)]
    class CollationCaseInsensitive : SQLiteFunction {
        public override int Compare(string param1, string param2) {
            return String.Compare(param1, param2, true);
        }
    }       

TOUPPER.RegisterFunction(typeof(TOUPPER));

solved in this way, but also mono c # 'using the library, here is how I need to do Android.Database.Sqlite.SQLiteDatabase

Answer 1

From SQL As Understood By SQLite, section "The LIKE and GLOB operators":

The LIKE operator is case sensitive by default for unicode characters that are beyond the ASCII range.

This means that "İ" is different from "i" and "I".

Answer 2

One solution for such a problem is to saved a normalized version of the text into another column. Before you INSERT the text you replace all special characters with some common character and put both versions in the database.

Your table looks like that then

ID   musteri     musteri_normalized
---  ----------  ------------------
1    İrem        Irem              
2    Kadir       Kadir             
3    yapılcağ    yapilcag 

Now you can use LIKE comparison on the normalized column and still return the real text from the database.

SELECT musteri FROM table WHERE musteri_normalized LIKE '%ir%';
-> İrem, Kadir

Answer 3

public class Sqlite_DB
{   
    private SqliteConnection CON;
    public  SqliteCommand COM;



    string dbName = System.IO.Path.Combine(@"sdcard", @"testDB.db3");

    public Sqlite_DB()
    {
        TOUPPER.RegisterFunction(typeof(TOUPPER));
        CollationCaseInsensitive.RegisterFunction(typeof(CollationCaseInsensitive));
        CON=new SqliteConnection(String.Format("Data Source={0};Pooling={1}", dbName, false));
        COM=new SqliteCommand(CON);

    }
    public void close()
    {
        COM.Clone();
        CON.Clone();
    }
    public void open()
    {
        CON.Open();
    }

}

#region TOUPPER
[Mono.Data.Sqlite.SqliteFunction(Name = "TOUPPER", Arguments = 1, FuncType = FunctionType.Scalar)]
public class TOUPPER: Mono.Data.Sqlite.SqliteFunction
{
    public override object Invoke(object[] args)//characters for the growth of
    {
        return args[0].ToString().ToUpper();
    }
}       

[Mono.Data.Sqlite.SqliteFunction(Name = "COLLATION_CASE_INSENSITIVE", FuncType = FunctionType.Collation)]
class CollationCaseInsensitive : Mono.Data.Sqlite.SqliteFunction
{
    public override int Compare(string param1, string param2) //According to Turkish character sorting to patch
    {
        return String.Compare(param1, param2, true);
    }
} 
#endregion







public class TEST_X
{
    string strValue="ir";//test
    public void MUSTERI()
    {
        string srg="select * from "+Cari_._
                +"where TOUPPER(musteri) like '%"+strValue.toUpper()+"%';";

        try {
            Sqlite_DB d=new Sqlite_DB();
            d.open();

            d.COM.CommandText=srg;

            SqliteDataReader dr=d.COM.ExecuteReader();

            while (dr.Read()) 
            {

                Android.Util.Log.Error(">>>>",dr[0].ToString()+"<<<");

            }
            d.close();

        } catch (Exception ex) {
            Android.Util.Log.Error(">>>>",ex+"<<<");
        }

    }
}


ID   musteri    
---  ---------- 
1    İrem                   
2    Kadir                   
3    Demir

returning result:

-İrem

-Kadir

-Demir

it works in mono ...

Answer 4

It is not the best solution. But i have created a workaround to solve this problem.

You can found it here: http://codelama.com/sqlite-turkce-harf-siralamasi-sqlite-turkish-collation/

Because of this solution needs UTF8CI named collation, i also make a little SQLite Admin. It also can be found here on github

I tried to use icu, compiling from source etc. This was the simplest solution for me. I hope it helps.

Steps to make it work: 1. Add this code anywhere in you namespace: [SQLiteFunction(FuncType = FunctionType.Collation, Name = "UTF8CI")] public class SQLiteCaseInsensitiveCollation : SQLiteFunction { private static readonly System.Globalization.CultureInfo _cultureInfo = System.Globalization.CultureInfo.CreateSpecificCulture("tr-TR"); public override int Compare(string x, string y) { return string.Compare(x, y, _cultureInfo, System.Globalization.CompareOptions.IgnoreCase); } }

2. In your program.cs, just before open first form insert this code: System.Data.SQLite.SQLiteFunction.RegisterFunction(typeof(SQLiteCaseInsensitiveCollation));

Now, you will have Turkish collation. To make this working, you have to add "COLLATE UTF8CI" after your text column definition. Like this: CREATE TABLE maytable ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, mytextfield1 TEXT NULL COLLATE UTF8CI, mytextfield2 TEXT)

If you receive "No such collation: UTF8CI", add "COLLATE BINARY" to the end of query. Like this: select * from mytable order by mytextfield COLLATE BINARY