SQLite 3 Version Sort String

Question

It must be a simple thing but I cannot figure out even looking at lots of examples here at Stackoverflow.

I have this string sequence, generated by a SQLite select:

SELECT sort FROM collections ORDER BY sort

1
10
11
14
15
2
3
4
5
6
6.12
6.7
6.7.13
6.7.8
9

But I need in this order ( sort content is like version numbers ):

Please, how to do this with a SQLITE 3 select ? Best regards

What type is sort in the table definition? Check `.schema collections` in the sqlite shell. — Schwern, Feb 02 '15 at 17:30
possible duplicate of [SQL ORDER chars numerically](http://stackoverflow.com/questions/489874/sql-order-chars-numerically) — Schwern, Feb 02 '15 at 17:38
Sort = Text in Table Definition. I checked the link you suggested. Doesn't work in my case because I have levels... — Britto, Feb 02 '15 at 17:48

Schwern · Answer 1 · 2015-02-02T18:15:18.860

Version sorting in SQL is not easy. It's even harder in SQLite because it doesn't have many functions to do string processing (there's no split).

If there's not too much data, you can do it in the programming language which is retrieving the data. This is a problem because it requires you to fetch all the data into memory.

You can provide your own custom collation to SQLite. How you do this depends on what programming language and driver you're using to run the query. Here's an example using Perl and DBD::SQLite.

use DBD::SQLite;
require version;

$DBD::SQLite::COLLATION{as_versions} = sub {
    my($a,$b) = @_;
    return version->parse($a) <=> version->parse($b);
};

my $dbh = DBI->connect(...);
my $sth = $dbh->prepare(q{
    SELECT sort
    FROM   collections
    ORDER BY sort COLLATE as_versions
});

Finally, you can alter your data to a form better suited to SQLite. When inserting the version, add another column which is the version converted to a number. This is typically done by converting each part into thousandths, millionths, billionths and so on. For example, "6.7.13" becomes "6.007013" and "6.12" becomes "6.012".

Thanks @Schwern It almost there, but I received this: 6.12 / 6.7 / 6.7.8 / 6.7.13 - Please note that 6.12 must come after 6.7.13 — Britto, Feb 02 '15 at 17:47
@Britto Sorry, I misunderstood your question. I've altered my answer to match. — Schwern, Feb 02 '15 at 18:15

score 0 · Answer 2 · answered Jan 28 '23 at 12:58

This query parses string versions into three columns with int versions and sorts the version numbers in ascending order, from the major version number to the build number.

 SELECT *, 
    CAST(SUBSTR(version, 1, INSTR(version, '.')-1) AS INTEGER) AS major,  
    CAST(SUBSTR(SUBSTR(version, INSTR(version, '.')+1) ,  1, INSTR(SUBSTR(version, INSTR(version, '.')+1), '.')-1) AS INTEGER) AS minor, 
    CAST(SUBSTR(SUBSTR(version, INSTR(version, '.')  + 1) , INSTR(SUBSTR(version, INSTR(version, '.')  + 1) , '.')  + 1) AS INTEGER) AS build
    FROM collections 
    ORDER BY major DESC, minor DESC, build DESC

SQLite 3 Version Sort String

2 Answers2