Return sqlite DB without case sensitivity

Question

I'm sitting here wondering how to sort my database without case sensitivity. Let's say I have 'A', 'a', 'B', 'b'. It will print like this: 'A', 'B', 'a', 'b'. but I want it sorted in a more human way. Here's my Cursor function:

/**
* Return a Cursor over the list of all brands in the database
* 
* @return Cursor over all brands sorted alphabetically 
+ TODO: doesn't sort alphabetically because it's case sensitive
*/
public Cursor fetchAllBrands() {

return mDb.query(Constants.DATABASE_TABLE, 
    new String[] { 
        Constants.KEY_ROWID, Constants.KEY_BRAND, Constants.KEY_STOCK 
    }, 
    null, null, null, null, Constants.KEY_BRAND);
}

I have noticed that Java has CASE_INSENSITIVE_ORDER, but I have no idea how to implement it if that is the solution to this problem. Can anybody help me with this?

score 5 · Accepted Answer · edited May 23 '17 at 12:16

5

It was answered here. Unfortunately is seems that you have to use the rawQuery() method instead of using the query() wrapper method. This method allows you to input raw SQL code that will be executed like so ...

db.rawQuery("SELECT " + Constants.KEY_ROWID + "," + Constants.KEY_BRAND + "," +
             Constants.KEY_STOCK +
           " FROM " + Constants.DATABASE_TABLE +
           " ORDER BY " + Constants.KEY_BRAND + " COLLATE NOCASE ASC;", null);

edited May 23 '17 at 12:16

Community

1
1

answered Jun 03 '12 at 21:42

Stefan Bossbaly

6,682
9
53
82

I was hoping that I wouldn't have to use rawQuery. But I guess that's what I'll have to do. Thanks for the reply - I'll test it when I get home in some hours. – ringkjob Jun 03 '12 at 21:55

score 3 · Answer 2 · answered Jun 03 '12 at 21:41

Try a rawQuery(...) using COLLATE NOCASE. Example...

return mDb.rawQuery("SELECT " + Constants.KEY_ROWID + ","
              + Constants.KEY_BRAND + ","
              +  Constants.KEY_STOCK
              + " FROM " + Constants.DATABASE_TABLE
              + " ORDER BY " + Constants.KEY_BRAND + " COLLATE NOCASE", null);

score 1 · Answer 3 · answered Sep 08 '14 at 12:14

Try like this

public Cursor fetchAllBrands() {

return mDb.query(Constants.DATABASE_TABLE, 
new String[] { 
    Constants.KEY_ROWID, Constants.KEY_BRAND, Constants.KEY_STOCK 
}, 
null, null, null, null, Constants.KEY_BRAND + " COLLATE NOCASE ASC");
}

Return sqlite DB without case sensitivity

3 Answers3