I'm using sqlite. I have no problems except sorting. When I query a table using the "order by" clause I can not get the right sorting according to iso8859-9 charset. The special characters of my encoding are seen at the end of the list. It is like:
- asdf
- bnm
- cgfj
- dfgh
- çdfg
But what I want is:
- asdf
- bnm
- cgfj
- çdfg
- dfgh
What can I do to overcome this problem?
Thanks in advance.
using:
windows, netbeans, java, sqlite
You can define a new collating sequence, see Define New Collating Sequences if you would like to solve the sorting from sqlite. Something very similar to Case-Insensitive Sort of UTF8 Data Using System.Data.SQLite. In your case, probably, you need to use: CultureInfo.CreateSpecificCulture("tr-TR"); instead of CultureInfo.CreateSpecificCulture("ru-RU");.
Something like:
using System.Data.SQLite;
using System.Globalization;
namespace SQLiteUTF8CIComparison {
/// <summary />
/// This function adds case-insensitive sort feature to SQLite engine
/// To initialize, use SQLiteFunction.RegisterFunction()
/// before all connections are open
/// </summary />
[SQLiteFunction(FuncType = FunctionType.Collation, Name = "UTF8CI")]
public class SQLiteCaseInsensitiveCollation : SQLiteFunction {
/// <summary />
/// CultureInfo for comparing strings in case insensitive manner
/// </summary />
private static readonly CultureInfo _cultureInfo =
CultureInfo.CreateSpecificCulture("tr-TR");
/// <summary />
/// Does case-insensitive comparison using _cultureInfo
/// </summary />
/// Left string
/// Right string
/// <returns />The result of a comparison</returns />
public override int Compare(string x, string y) {
return string.Compare(x, y, _cultureInfo, CompareOptions.IgnoreCase);
}
}
}
A simple alternative, if your requirements will allow it, is to retrieve the data unsorted from sqlite and sort in Java. Note that using this approach is suitable only if you are not selecting a huge (millions) number of records, since it will consume a lot of memory.
