Android SQLite ORDER BY COLLATE "NOCASE" and "LOCALIZED" together

Question

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!

Answer 1

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;
}

Answer 2

This works for me:

field COLLATE LOCALIZED, field COLLATE NOCASE