Sqlite Query in non case sensitive alphabetical order

From the trenches

Beware of the syntax booby trap

Beware of accidentally tripping over "near COLLATE: syntax error". It's the pesky syntax mistake that occurs if COLLATE NOCASE is misplaced. Remember, it should be snug right after your column name and cosy before ASC or DESC.

Alternative: lowercase all the things

If for some hair-splitting reason COLLATE NOCASE is off the table, the SQLite lower() function is your best pal:

SELECT * FROM MyTable ORDER BY lower(MyColumn);


However, bear in mind that lower() might steal your indexes' utility, a bit like stealing candy from a baby. And we wouldn't want that, would we? 😛

Android's gift to SQLite

If you're working with SQLite via Android, you'll use the db.rawQuery method to execute your thoroughly thought out query. Here's how:

Cursor cursor = db.rawQuery("SELECT * FROM table ORDER BY column COLLATE NOCASE", null);


Rise up, it's ASC

SQLite has a default ordering: ASC. Leaving it out keeps your query short and sweet. But if you're feeling verbose (it happens to the best of us), feel free to include it.

Useful specifics and scenarios

Unicode goodies

Before you pop the champagne, remember that COLLATE NOCASE can play hooky with non-ASCII characters in Unicode strings, as it's strictly an English-only function. Consider brushing up on your Unicode knowledge before waltzing into that arena.

Performance check

Although COLLATE NOCASE doesn't eat all the cheese (it tries to leave some for the rest), if you're working with large datasets, make sure you have an index in your bag of tricks that also employs COLLATE NOCASE. Because nobody likes a slow-query partypooper. 🎈

PRAGMAs: The hidden gems

For those who enjoy messing with system behavior (we know you're out there!), the PRAGMA statements in SQLite are like an all-access backstage pass. Something like PRAGMA case_sensitive_like=ON; can flip the LIKE clause behavior, making it case sensitive.