All geek questions in one place

Turkish character in SQLite when using LIKE clause

select *from urunler where musteri like %ir%;

test data:

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

returning result:

 Kadir Demir

if you use %İr% , then Yrem will return, but Qadir and Demir will not return. There is a problem in other Turkish characters, but not some 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));

but also mono C # 'using the library, here is how I need to do Android.Database.Sqlite.SQLiteDatabase

+6
source share
4 answers

One solution to this problem is to save the normalized version of the text in another column. Before the INSERT text, you replace all special characters with some common character and put both versions in the database.

Your table looks like this:

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

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

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

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

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

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

+5
source 
 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

he works in mono ...

+3
source

This is not the best solution. But I created a workaround to solve this problem.

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

Because of this solution, UTF8CI with the sort name is required, I am also a bit SQLite administrator. It can also be found here on github.

I tried to use icu, compile from source code, etc. It was the easiest solution for me. I hope this helps.

Steps to get it working: 1. Add this code anywhere in your 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); } } [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); } } [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); } } [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); } } [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); } }

  1. In your program.cs, before opening the first form, paste this code: System.Data.SQLite.SQLiteFunction.RegisterFunction(typeof(SQLiteCaseInsensitiveCollation));

You will now have Turkish sorting. For this to work, you must add "COLLATE UTF8CI" after defining your text column. For example: CREATE TABLE maytable ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, mytextfield1 TEXT NULL COLLATE UTF8CI, mytextfield2 TEXT)

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

0
source

Source: https://habr.com/ru/post/914322/

More articles:


All Articles