SQLite Query Alphabetical Case-insensitive

Question

All I want to do is grab the material in alphabetical order and ignore the capital letters.

db.rawQuery("SELECT " + catName + " FROM "+tableName+" ORDER BY "+catName+" ASC COLLATE NOCASE;", null); 

This is the code that I use above, but it always gives me an SQLite exception saying that COLLATE is a syntax error.

android.database.sqlite.SQLiteException: next to "COLLATE": syntax error: when compiling: SELECT Artist FROM testTable COLLATE NOCASE ASC

Answer 1

COLLATE goes to the direction of the order:

 db.rawQuery("SELECT " + catName + " FROM " +tableName +" ORDER BY "+catName+" COLLATE NOCASE ASC;", null); 

But you do not need ASC - by default, so you can just use:

 db.rawQuery("SELECT "+ catName +" FROM "+ tableName +" ORDER BY "+ catName +" COLLATE NOCASE;", null); 

Answer 2

add COLLATE NOCASE after OrderBy line.

db.query (table, columns, selection, selectionArgs, groupBy, having, orderBy + "COLLATE NOCASE ASC");

here, the order from ASC or DESC depends on your needs.

Answer 3

This should work too:

 db.rawQuery("SELECT "+ catName +" FROM "+ tableName +" ORDER BY lower("+ catName +");", null);