LIKE case-insensitive for not English letters

I tried it with using COLLATE NOCASE added to the column definition. If you are able to do it, then this is the way to go.

What I tested:

CREATE TABLE PartnersTable     (`id` , `TitleColumn` COLLATE NOCASE );

Added same date as in your question:

INSERT INTO PartnersTable    (`id`, `TitleColumn`)VALUES (1, 'Газпром'), (2, 'Лукойл'), (3, 'Йотафон'), (4, 'Мечта ювелира');

Then tried to select it:

select *from PartnersTablewhere TitleColumn like '%Ювелир%'

It works.

Demo here:http://sqlfiddle.com/#!7/ae8f8/2

Edit:You can also use it with without UPPER. By default LIKE is case insensitive. As per documentation:

Any other character matches itself or it's lower/upper case equivalent (i.e. case-insensitive matching). (A bug: SQLite only understands upper/lower case for ASCII characters. The LIKE operator is case sensitive for unicode characters that are beyond the ASCII range. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE.)."

Demo here:http://sqlfiddle.com/#!7/c9b5d/1

The code is not working because UPPER is working for only ASCII characters(link). To solve this I implemented User-Defined function with BindFunction for .NET as below.

// for System.Data.SQLiteconnection.BindFunction(    new SQLiteFunctionAttribute("myupper", 1, FunctionType.Scalar),    (Func<object[], object>)((object[] args) => ((string)((object[])args[1])[0]).ToUpper()),    null);        // for Microsoft.Data.Sqliteconnection.CreateFunction(    "myupper",    (string arg) => (arg).ToUpper());var command = connection.CreateCommand();command.CommandText =@"    SELECT *    FROM PartnersTable     WHERE MYUPPER(TitleColumn) LIKE MYUPPER('%pattern%')";      command.ExecuteQuery()

However it takes longer to execute the query.

Following links might help too

https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite/user-defined-functionshttps://docs.microsoft.com/en-us/dotnet/standard/data/sqlite/compare

Redifine Like function (This uses the Sqlite .NET wrapper). Also note VB like uses asterisk wildcard. If you want % replace the string or redefine GLOB instead.

     public static void OverrideLikeFunction() {    SQLiteSpace.SQLiteFunction.RegisterFunction(typeof(SQLiteHelper.Ulike));}[System.Data.SQLite.SQLiteFunction(FuncType=System.Data.SQLite.FunctionType.Scalar, Name="Like")];        Public Class Ulike;            Inherits System.Data.SQLite.SQLiteFunction;            Public Overrides Function Invoke(args() As object) As object;            try            {                    if (System.Convert.IsDBNull(args(0)) || System.Convert.IsDBNull(args(1)))                    {                        return false;                    }                    return (Microsoft.VisualBasic.CompilerServices.StringType.StrLike(System.Convert.ToString(args(1)).ToUpperInvariant(), System.Convert.ToString(args(0)).ToUpperInvariant(), Microsoft.VisualBasic.CompareMethod.Binary));                }                catch (Exception ex)                {                                   return null;                }            }        }