LIKE case-insensitive for not English letters

Question

I want to retrieve rows whose TITLE field meets some pattern (case-insensitive), and this field contains only non-English letters.

I tried this:

SEARCH * FROM TABLE_NAME WHERE UPPER(column_name) LIKE UPPER('%pattern%');

However, this doesn't work, may be because the table contains only non-English letters.

UPDATE:

Example:

SELECT * FROM PartnersTable WHERE UPPER(TitleColumn) LIKE UPPER('%pattern%');

Where:

TitleColumn may contain "Газпром", "Лукойл", "Йотафон", "Мечта ювелира",

pattern may contain "газ", "Ювелир" etc.

Answer 1

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 data as in your question:

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

Then tried to select it:

select *
from PartnersTable
where 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

Answer 2

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.SQLite
connection.BindFunction(
    new SQLiteFunctionAttribute("myupper", 1, FunctionType.Scalar),
    (Func<object[], object>)((object[] args) => ((string)((object[])args[1])[0]).ToUpper()),
    null);
        
// for Microsoft.Data.Sqlite
connection.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-functions https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite/compare