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