Custom collation sequences

Brief

Collation sequence is an algorithm definition that is used by SQLite to compare two values and find out if one is less/greater/equal to another.

Collations can be used in several contexts by SQLite - for example table's column can be defined with a specific collation, which will affect how is sorting done for that column, how is grouping by that column done, etc. See SQLite documentation for more details.

SQLiteStudio supports defining user's own collation sequences.

Defining your own collation sequences

To add/edit/delete user's custom collation sequence, open Collations editor from te main toolbar, main menu or by configured hotkey (by default it's Alt+3, or Option+3 on macOS).

Function-based collation implementation is like writing custom SQL function implementation with 2 rules:

• The collation function will always be provided with exactly 2 arguments - values to compare.
• In addition to "exactly 2 arguments", there will be parameters/variables defined under names first and second having the very same values as mentioned 2 arguments.
• The collation function has to return -1, 0, or 1, when first argument is less then, equal or greater than second argument.

Example (JavaScript):

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

Example using the first and second variables (JavaScript):

return first.localeCompare(second, 'pl_PL');

Extension-based collation

Extension-based collation is implemented by an SQLite extension. You have only to provide its registration code in Collations Editor window (pick "Extension-based" from the dropdown list on right side of the window).

Example (ICU extension):

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