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)

Answer 1

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:

1. Download ICU extension for sqlite

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

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

4. 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).

5. Now we can sort data with our new locale:

   SELECT * FROM some_table ORDER BY name COLLATE POLISH;
   

   And it is case insensitive!