System.Data.SQLite

sqlite use memcmp to compare string, it works fine for english char, but not for chinese.

how can I let it sort by chinese?

You can override the built-in function "lower":

        [SQLiteFunction(Name = "lower", Arguments = 1, FuncType = FunctionType.Scalar)]
        internal class LoverFunction : SQLiteFunction
        {
            public override object Invoke(object[] args)
            {
                Debug.Assert(args != null && args.Length == 1 && args[0] is string);
                return ((string)args[0]).ToLower();
            }
        }
 

Then use

SELECT * FROM SomeTable WHERE lower(Name) LIKE lower(@p)

i don't thinks this can resolve problem.and comparation is not associate with case sense, string store in sqlite is correct, but it's memcmp reture wrong result.

You'll have to use a custom collation sequence and modify it for your needs ... an example:

/// <summary>
/// User-defined collating sequence using the current UI culture.
/// </summary>
[SQLiteFunction(Name = "MYSEQUENCE", FuncType = FunctionType.Collation)]
class MySequence : SQLiteFunction
{
  public override int Compare(string param1, string param2)
  {
    return String.Compare(param1, param2, true);
  }
}

Your SQL query to use the custom collation sequence above might look like this:

SELECT * FROM MyTable ORDER BY MyChineseColumn COLLATE MYSEQUENCE DESC