Can SQLite sort naturally?

Question

Can SQLite sort naturally? For example,

CREATE TABLE animals (
    id INTEGER NOT NULL PRIMARY KEY,
    name TEXT NOT NULL
);

INSERT INTO animals (name) VALUES ('Monkey');
INSERT INTO animals (name) VALUES ('manatee');

SELECT name FROM animals ORDER BY name;

name      
----------
Monkey    
manatee

I would prefer the results to be sorted naturally (i.e., manatee, Monkey). Does SQLite not have an option to sort like this? I sort a lot of data, and if SQLite cannot sort naturally, I suppose the solution is to head back to PostgreSQL or MySQL.

Solution 1

ORDER BY UPPER(name) will accomplish what you're looking for.

Additionally, you're using the SQLite default collation, which means that comparisons are done using C's memcmp function, which compares bytes. In this case, M and m are very different. You can alter the column to have a NOCASE collation. Though, looking over the docs, it appears that you'll have to create a new table, copy your data into it, drop the old table and rename the new one, since the ALTER TABLE command only renames the table or adds a column.

Solution 2

You don't have to alter the table to change collation:

SELECT name FROM animals ORDER BY name COLLATE NOCASE;

If you need more specialized sorting, you can register your own collation functions with the sqlite3_create_collation* family of functions and then use COLLATE myCollationFunctionName.

Comments

[Admin]

Not exactly natural (it still sorts 1, 10, 11, 12, 2, 3, etc.), but that works for the table I'm working with. Thanks!

[colithium]

I wish SQLite would fully support ALTER syntax, it's very annoying

[Eric]

@Mark: Some people do consider that natural ordering (e.g.-movie titles). If you'd like to get ideas on how to sort numerically in SQLite, please post a different question, since the parameters around it are a bit different, and it would attract those who are more familiar with SQLite than I.

[laalto]

No ALTER needed, you can specify collation directly in a select statement. See my answer below.

[Eric]

@laalto: Yes, but it still only works on a per query basis, not at the table level.

[Torsten Römer]

For people using SQLite in Android, I'd like to mention that Android adds COLLATE UNICODE, which also sorts umlauts like "ä" correctly while both UPPER() and COLLATE NOCASE operate only on ASCII characters.