Case insensitive order by with ormlite and sqlite in android

Question

I want to order my data objects from an ORMLite DAO case insensitively.

Currently I am using the following sqlite code to order my owner items case sensitively:

ownerDao.queryBuilder().orderBy("Name", true).query();

I see here that sqlite supports case insensitive "order by" with the following raw SQL:

SELECT * FROM owner ORDER BY Name COLLATE NOCASE

Any easy way (easier than calling queryRaw()) of adding the desired suffix?

Could an alternative solution be to set the columnDefinition property on the DatabaseField annotation to TEXT COLLATE NOCASE?

have you tried `orderBy("Name COLLATE NOCASE", true)` maybe it goes through that way. — zapl, Apr 23 '12 at 13:13
I thought about that, but even if that works, it might break with some future version of ORMLite. — Diederik, Apr 23 '12 at 13:32

score 17 · Accepted Answer · edited Mar 22 '14 at 18:30

I think what you want is to use the QueryBuilder.orderByRaw(String) method. It would look something like:

ownerDao.queryBuilder().orderByRaw("Name COLLATE NOCASE").query();

Notice the raw string does not contain the ORDER BY part of the string. To quote the javadocs:

Add raw SQL "ORDER BY" clause to the SQL query statement. This should not include the "ORDER BY".

The TEXT COLLATE NOCASE columnDefinition might work although I don't have any experience with using that at all.

Also, I would not use the orderBy("Name COLLATE NOCASE", true) answer since typically ORMLite tries to escape the column names and the quotes would be in the wrong place.

orderByRaw("Name COLLATE NOCASE") works as expected. Thanks Gray — Diederik, Apr 24 '12 at 06:40

Case insensitive order by with ormlite and sqlite in android

1 Answers1

Linked