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.

Answer 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.

Answer 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.