SQLite ordering column with accented characters

Question

I'm developping a website using SQLite databases with PHP. I'm running Windows (dev) and my production environment should be a *nix platform. Here is the schema of my table :

CREATE TABLE [animals](
    [id] INTEGER NOT NULL UNIQUE, 
    [name] VARCHAR(50) NOT NULL
);

I want to sort the animals by name (contains accented characters). My SQL query is:

SELECT * FROM animals ORDER BY name DESC

and I get :

éléphant
tigre
renard
chien

instead of,

tigre
renard
éléphant
chien

I've searched on the web. I tried,

SELECT * FROM animals ORDER BY name COLLATE *binary|nocase|rtrim* DESC

but I have the same problem. I also tried,

SELECT * FROM animals ORDER BY name COLLATE *localized|unicode* DESC

But I get an error (either my SQLite client crashes, either PHP returns the following error: no such collation sequence: UNICODE).

It seems there's a solution for Android, but I'm running Windows and my production environment should be a *nix platform.

How can I get my animals sorted the right way?

Answer 1

By default, SQLite has only ASCII collations.

It would be possible to use the ICU extension to get Unicode support, but PHP did not enable this.

But you can create your own collation and implement the comparison in PHP.