Is there any way to order dataset with locale alphabetical order without case sensitivity? I have found "COLLATE LOCALIZED", "COLLATE NOCASE", but "COLLATE LOCALIZED NOCASE" shows error (also in the reverse order).
Thanks for any response!
Is there any way to order dataset with locale alphabetical order without case sensitivity? I have found "COLLATE LOCALIZED", "COLLATE NOCASE", but "COLLATE LOCALIZED NOCASE" shows error (also in the reverse order).
Thanks for any response!
I didn't find any easy way. I created additional sorting table columns, which contain normalized data (noted here). Created constant for normalized table columns name prefix:
public static final String ASCII_PREFIX = "ascii_";
created table (for text column create one more column with prefix):
String sql = "CREATE TABLE IF NOT EXISTS " + tableName + " (";
for( columnModel column: table.columns ) {
if( column.position > 0 ){ sql += ", "; }
sql += column.dbName + " " + column.dbType;
}
for( columnModel column: table.columns ) {
if( column.dbType.equals("TEXT") ) {
sql += ", " + ASCII_PREFIX + column.dbName + " " + column.dbType;
}
}
sql += ")";
db.execSQL(sql);
changed table data filling (add normalized data for special column):
//...cycle for data filling...
ContentValues values = new ContentValues();
for( columnModel column: table.columns ) {
values.put(column.dbName, valueStr);
if( column.dbType.equals("TEXT") ){
values.put(ASCII_PREFIX + column.dbName, normalize(valueStr));
}
}
db.insert(tableName, null, values);
and then I sort alternatively for text columns:
//... in method for sorting...
String orderBy;
....
if( column.dbType.equals("TEXT") ) {
orderBy = ASCII_PREFIX + column.dbName;
}else {
orderBy = column.dbName;
}