SQLite case-insensitive like with non ascii chars

ServiceStack.OrmLite
1

Hello!

First of all I would like to wish you a happy new year.
I use SQLite with OrmLite and try to make case insensitive search with non ascii characters.
I know the SQLite doesn’t support this by default. I try to implement SQLiteFunction but I don’t know where should I register that function (I tried in apphost but not working for me).
Here is a fragment from the generated sql statement.

...upper("Protocol"."ShortName") like @0 LIMIT 100 PARAMS: @0=%ÉP%

The above sql query only filter records where ShortName contains É + p or P (e.g. CsapatÉpítés sablon-1) but doesn’t contains é + p OR P (e.g. Csapatépítés sablon-2)

Thank you,
Tom

2

I don’t understand what the question is?

3

The question is how can I write query which support case-insensitive like, upper statements.
This is the relevant part of my sql expression.

query.Where(x => x.ShortName.Contains(filter.ShortName));

This code generate sql statement that I wrote in my first post but that query is case-sensitive in SQLite.

4

You can’t change the behavior of Typed SqlExpressions, the only configuration that affects behavior is:

OrmLiteConfig.StripUpperInLike = true;

Which will not include the UPPER() in its LIKE expressions.

You’d need to use custom SQL for any custom SQL, e.g:

q.Where("ShortName LIKE {0}", filter.ShortName);

The Table and Column APIs can also be used to provide a Typed API to generate Table/Column names if needed.

5

SQLite supports to redefine built in functions (e.g. upper), collations.

Case-insensitive LIKE in SQLite

How can I use custom SQLite functions with OrmLite?

6

SQLite functions are unknown and unrelated to OrmLite, your linked post shows how to use “System.Data.SQLite” APIs to register a custom function, once registered it’s presumably able to use your custom function in SQL that SQLite executes.

This operates at the SQL level, there’s nothing you need to register with OrmLite which is oblivious to any function extensions.

7

I figured it out! I had to override the Invoke method in the custom SQLiteFunction.

Thank you for your time.