SQLite Query in non case sensitive alphabetical order

android sql sqlite sql-order-by collate
Anthony Honciano picture Anthony Honciano · May 11, 2011 · Viewed 41.2k times · Source

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

Answer

OMG Ponies picture OMG Ponies · May 11, 2011

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

Related questions

SQLite in Android How to update a specific row

I've been trying to update a specific row for a while now, and it seems that there are two ways to do this. From what I've read and tried, you can just use the: execSQL(String sql) method or the: …

Read More
Best way to work with dates in Android SQLite

I'm having some trouble working with dates on my Android application that uses SQLite. I have a couple questions: What type should I use to store dates in SQLite (text, integer, ...)? Given the best way to store dates how do …

Read More
Android SQLite: Update Statement

I need to implement SQLite in my application. I followed this tutorial.. Creating and using databases in Android one Everything is working fine. I inserted 1 row with 5 columns. Now I want to update value of 1 column only and others will …

Read More