custom COLLATE

[githubuser181226]

Hi.

By default SQLite sorting doesn't account for accented letters (ie. Polish letters: Ł, Ś, Ź..,) and if a string begins with one, ORDER BY ASC clause always puts those at the very end, which is a game breaker for me.

Is it possible to create a custom function to account for accented letters to use it as a COLLATE method and with the custom sorting functionality of the UI (ie. double-clicking on column header)?

So far I've only managed to create a simple javascript function which replaces all the accented letters in a string with ASCII non-accented equivalents, which then I can use in ORDER BY clause, but although it is somewhat helpful (values starting with accented letters are no longer at the end of the sorted list), it is far from optimal, because sorting still isn't correct and I tried using the function in the COLLATE clause, but it doesn't work.

And I can't find any documentation showing how to create javascript custom collate methods, or if that is even possible.

I'm using SQLiteStudio in Windows as a standalone DB manager for storing and managing data.

Perhaps there is an extension already to fix my issue.
Thank you in advance.

[tuffnatty]

Have you already tried the icu extension and the Collation editor menu option?

[githubuser181226]

Have you already tried the icu extension and the Collation editor menu option?

Hi. Thank you for you reply.

I have ICU extension installed and and have Collation Editor in the menus.
I tried it but haven't been able to figure out how. The documenation lacks explanation on how to use it, and shows no working example. In the end I gave up.

Thankfully I then went to sqlite documentation, specifically to ICU readme file, and found out about icu_load_collation command, so I started SQL editor and ran:

SELECT icu_load_collation('pl_PL', 'POLSKI');

which created collation named POLSKI, which I then used in my tables, and voila - I now have proper sorting. Exactly what I wanted.

However, as I restarted the program it told me no such collation: POLSKI forcing me to re-run above SQL command.

Is there a way to make a collation created with icu_load_collation persistent or to auto-execute SQL commands on program startup?

Please, advise.
Thank you.

[tuffnatty]

Is there a way to make a collation created with icu_load_collation persistent

No.

or to auto-execute SQL commands on program startup?

I don't think it's supported.

Probably in your simple case a simple JavaScript collation definition would be enough (but probably much slower than ICU):

return arguments[0].localeCompare(arguments[1], 'pl')

[pawelsalawa]

I think you may try using WxSQLite3 plugin for the database connection and then add the SELECT icu_load_collation('pl_PL', 'POLSKI'); to initialization/configuration SQL, while keeping the "password" empty, to avoid encrypting with WxSQLite3, somthing like this: