SQLite Forum

Natural sort order
Login
Two questions:

First, the PG solutions all seem to define a new SQL function that returns
a sort key.  This is fine, but it seems more "natural" to me to create a
new collating sequence.  So the PG solution is:

~~~
    SELECT * FROM user ORDER BY naturalsort(name);
~~~

But wouldn't it be nicer to do:

~~~
    SELECT * FROM user ORDER BY name COLLATE natural;
~~~

Second thing:
I prefer the idea of "dictionary" sort order.
Dictionary order does the same magic with integers, but
also mixes case differences so that "a" comes after "A" but
before "B" rather than after "Z".  It seems like this is
even more natural that just putting numeric parts in numeric
order, does it not?

================================
| BINARY |   | DICTIONARY |
| B41    |   | a5         |
| C419   |   | B41        |
| C421   |   | C419       |
| a5     |   | c420       |
| c420   |   | C421       |
| d1234  |   | d34        |
| d234   |   | d234       |
| d34    |   | d1234      |
================================

If we add something like this to SQLite, I would want it to be
full "dictionary" order not "natural" order.  In other words, I
would want it to take case into account as a tie-breaker.