3

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?

Alexis Romot
  • 524
  • 6
  • 19
  • Look at [this answer](https://stackoverflow.com/a/58901967/111036) for how to enable the [SQLite "ICU" extension](https://www.sqlite.org/src/artifact?ci=trunk&filename=ext/icu/README.txt) to have correct sorting with accented characters. It is for a Debian-based system, but can easily be adapted for Windows I guess, by changing the `libicu.so` name to `libicu.dll`. – mivk Nov 17 '19 at 15:31

1 Answers1

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.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • is there a way to use ICU binary as SQLite extension? I'm currently trying to use Windows DLLs from the package icu4c-58_2-Win64-MSVC2015.zip, but without success... an help? – Alexis Romot Apr 05 '17 at 11:37