How to sort text in sqlite3 with specified locale?

Question

Sqlite3 by default sorts only by ascii letters. I tried to look in google, but the only thing I found were informations about collations. Sqlite3 has only NOCASE, RTRIM and BIARY collations. How to add support for a specific locale? (I'm using it in Rails application)

score 29 · Answer 1 · edited Jul 18 '14 at 17:42

29

I accepted Doug Currie answer, but I want to add some "algorithm" how to do it, because sqlite3 documentation is very strange (at least for me).

Ok, we have working sqlite3 and now:

Download ICU extension for sqlite
Compile it:
```
gcc -shared icu.c `icu-config --ldflags` -o libSqliteIcu.so
```
It is for Linux. I also needed to install additional ICU development package:
```
sudo apt-get install libicu-dev
```
I'm working on 64 bit architecture and I get error with __relocation R_X86_64_32S__ (whatever it means :). GCC suggested adding -fPIC to compile options and it helped.
Run sqlite3. We can load extension with command:
```
.load './libSqliteIcu.so'
```
Assuming that it is in the current directory, we can also specify whole path.
Create new collation:
```
SELECT icu_load_collation('pl_PL', 'POLISH');
```
The first parameter is desired locale and the second is it's (it can be whatever).
Now we can sort data with our new locale:
```
SELECT * FROM some_table ORDER BY name COLLATE POLISH;
```
And it is case insensitive!

edited Jul 18 '14 at 17:42

Changaco

790
5
12

answered Mar 05 '09 at 15:32

klew

14,837
7
47
59

I failed to build it on macOS 10.13. For all the documents I can google they all seem outdated. – Qiulang Sep 21 '18 at 04:01
install both ICU and SQLite3 development packages `sudo apt update && sudo apt install libicu-dev libsqlite3-dev -y` – pouya Jan 06 '19 at 07:05
1

Thank you. That was really helpful, even though it didn't work straight away. `.load libSqliteIcu` would give `Error: /usr/local/lib/libSqliteIcu.so: undefined symbol: sqlite3_sqliteicu_init`. After changing the name to `libicu.so`, it works. Details in an additional answer. – mivk Nov 17 '19 at 15:10
For those who got `Error: /path/to/libSqliteIcu.so: undefined symbol: sqlite3_sqliteicu_init`, a better solution than renaming the file is specifying the correct entry point, that is: `.load /path/to/libSqliteIcu.so sqlite3_icu_init` – André Chalella Sep 01 '23 at 02:57

score 13 · Accepted Answer · edited Feb 25 '14 at 17:34

13

SQLite supports integration with ICU. According to the Readme file, sqlite/ext/icu/README.txt the sqlite/ext/icu/ directory contains source code for the SQLite "ICU" extension, an integration of the "International Components for Unicode" library with SQLite.

1. Features

    1.1  SQL Scalars upper() and lower()
    1.2  Unicode Aware LIKE Operator
    1.3  ICU Collation Sequences
    1.4  SQL REGEXP Operator

edited Feb 25 '14 at 17:34

Martijn

13,225
3
48
58

answered Mar 05 '09 at 12:41

Doug Currie

40,708
1
95
119

1

The first link is password-protected now. – Alix Axel Sep 02 '13 at 00:02

score 2 · Answer 3 · answered Sep 01 '13 at 23:59

If you can't afford to compile the ICU extension you can have a UDF do the same. In PHP/PDO:

$pdo->sqliteCreateFunction('locale',
    function ($data, $locale = 'root')
    {
        static $collators = array();

        if (isset($collators[$locale]) !== true)
        {
            $collators[$locale] = new \Collator($locale);
        }

        return $collators[$locale]->getSortKey($data);
    }
);

Example usage:

SELECT * FROM "table" ORDER BY locale("column", 'pt_PT');

I don't expect this approach to be as efficient as the native extension but it is surely more portable.

score 1 · Answer 4 · answered Nov 17 '19 at 15:21

As Doug Currie answered, ordering accented characters correctly can be enabled by loading the SQLite "ICU" extension

The extension needs to be compiled, as explained in kiew's own answer here. However, the library name given in the ICU readme file and in kiew's answer didn't work for me. This other answer suggested using the name libicu.so instead.

So this is what worked for me on Ubuntu 16.04 and on Debian 9.8 Stretch:

sudo apt install libicu-dev libsqlite3-dev dpkg-dev gcc make
apt-get source sqlite3
cd sqlite3-*/ext/icu       # assuming you have only 1 sqlite3 source directory
gcc -shared icu.c `icu-config --ldflags` -fPIC -o libicu.so
sudo cp libicu.so /usr/local/lib/
sudo ldconfig

After that in sqlite3, you can

.load libicu
SELECT icu_load_collation('', 'ICU');

The 2 arguments to icu_load_collation are locale and a custom name. The locale seems to be optional and can be left empty. The custom name is then visible with

PRAGMA collation_list;

And it can be used as in

SELECT col FROM tbl ORDER BY col COLLATE ICU;

How to sort text in sqlite3 with specified locale?

4 Answers4

Linked

Related