SQLite Query in non case sensitive alphabetical order

Question

All I want to do is grab the stuff 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 I'm using above, but it always gives me an SQLite exception saying that COLLATE is a syntax error.

android.database.sqlite.SQLiteException: near "COLLATE": syntax error: , while compiling: SELECT Artist FROM testTable COLLATE NOCASE ASC

Solution 1

COLLATE goes before the order direction:

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

But you don't need the ASC -- that's the default so you could just as well use:

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

Solution 2

add COLLATE NOCASE after orderBy String.

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

here, order by ASC or DESC depends on your need.

Solution 3

This should work too I think:

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

Comments

[Anthony Honciano]

SOOOO WEIRD! I thought I tried the latter code and got the same error...but now it works...I probably just had it typed incorrectly (most likely... THANK YOU VERY MUCH!

[Anthony Honciano]

Another question please... Is there a way to ignore duplicates in SQLite?

[OMG Ponies]

@Anthony Honciano: Post a new question about ignoring duplicates - very likely yes, but I'd need to see the details about the duplication.