Sorting software versions

There may be a time where a software version is stored as a string. In order to sort it, it will need to be broken up into its individual parts (major, minor, patch and build) and then sorted.

Let’s give it some sample data:

CREATE TABLE versions (
  version VARCHAR(15));
 
INSERT INTO versions (version) VALUES
('25.0.1364.86'),
('25.0.1364.124'),
('23.0.1271.100')

The correct order of these versions should be:

23.0.1271.100
25.0.1364.86
25.0.1364.124

Let’s start by breaking up each part. We will use SUBSTRING_INDEX to accomplish this.

SUBSTRING_INDEX(str, delim, count) will return the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.

SELECT SUBSTRING_INDEX(version, '.', 1) major
FROM versions

This will return 25, 25 and 23. We now have the first part.

For the second and third parts, we need to use an inner SUBSTRING_INDEX. For the minor part, the inner SUBSTRING_INDEX locates and returns the first two parts (ie. 23.0). Then, the outer SUBSTRING_INDEX locates and returns the second part of the first two parts. The patch part is like the minor part, except we reverse the order of the count; which first returns 1271.100, then 1271.

SELECT SUBSTRING_INDEX(version, '.', 1) major,
SUBSTRING_INDEX(SUBSTRING_INDEX(version, '.', 2), '.', -1) minor,
SUBSTRING_INDEX(SUBSTRING_INDEX(version, '.', -2), '.', 1) patch
FROM versions

Finally, we need to get build part:

SELECT SUBSTRING_INDEX(version, '.', 1) major,
SUBSTRING_INDEX(SUBSTRING_INDEX(version, '.', 2), '.', -1) minor,
SUBSTRING_INDEX(SUBSTRING_INDEX(version, '.', -2), '.', 1) patch,
SUBSTRING_INDEX(version, '.', -1) build
FROM versions

We now have the the four parts stored as strings. We can’t however sort integers that are strings, so we will CAST the strings as UNSIGNED INTEGER‘s. This will be wrapped as a subquery so we can sort by the aliases.

1
2
3
4
5
6
7
8
9
10
11
12
13

SELECT v.version 
FROM   (SELECT version, 
               CAST(Substring_index(version, '.', 1) AS UNSIGNED INTEGER)  major, 
               CAST(Substring_index(Substring_index(version, '.' 
                   , 2 ), '.', -1) AS UNSIGNED INTEGER)                    minor, 
               CAST(Substring_index(Substring_index(version, '.' 
                   , -2 ), '.', 1) AS UNSIGNED INTEGER)                    patch, 
               CAST(Substring_index(version, '.', -1) AS UNSIGNED INTEGER) build 
        FROM   versions) v 
ORDER  BY v.major, 
          v.minor, 
          v.patch, 
          v.build

Result

|       VERSION |
-----------------
| 23.0.1271.100 |
|  25.0.1364.86 |
| 25.0.1364.124 |