How to compare version string ("x.y.z") in MySQL?

Assuming that the number of groups is 3 or less, you can treat the version number as two decimal numbers and sort it accordingly. Here is how:

SELECT 
ver,
CAST(
    SUBSTRING_INDEX(ver, '.', 2)
    AS DECIMAL(6,3)
) AS ver1, -- ver1 = the string before 2nd dot
CAST(
    CASE
        WHEN LOCATE('.', ver) = 0 THEN NULL
        WHEN LOCATE('.', ver, LOCATE('.', ver)+1) = 0 THEN SUBSTRING_INDEX(ver, '.', -1)
        ELSE SUBSTRING_INDEX(ver, '.', -2)
    END
    AS DECIMAL(6,3)
) AS ver2  -- ver2 = if there is no dot then 0.0
           --        else if there is no 2nd dot then the string after 1st dot
           --        else the string after 1st dot
FROM
(
SELECT '1' AS ver UNION
SELECT '1.1' UNION
SELECT '1.01' UNION
SELECT '1.01.03' UNION
SELECT '1.01.04' UNION
SELECT '1.01.1' UNION
SELECT '1.11' UNION
SELECT '1.2' UNION
SELECT '1.2.0' UNION
SELECT '1.2.1' UNION
SELECT '1.2.11' UNION
SELECT '1.2.2' UNION
SELECT '2.0' UNION
SELECT '2.0.1' UNION
SELECT '11.1.1' 
) AS sample
ORDER BY ver1, ver2

Output:

ver     ver1    ver2
======= ======  ======
1        1.000  (NULL)
1.01     1.010   1.000
1.01.03  1.010   1.030
1.01.04  1.010   1.040
1.01.1   1.010   1.100
1.1      1.100   1.000
1.11     1.110  11.000
1.2.0    1.200   2.000
1.2      1.200   2.000
1.2.1    1.200   2.100
1.2.11   1.200   2.110
1.2.2    1.200   2.200
2.0      2.000   0.000
2.0.1    2.000   0.100
11.1.1  11.100   1.100

Notes:

1. You can extend this example for max 4 groups or more but the string functions will get more and more complicated.
2. The datatype conversion DECIMAL(6,3) is used for illustration. If you expect more than 3 digits in minor version numbers then modify accordingly.

I just use the following which works for all version numbers up to 255:

Compare example:

SELECT * FROM versions
WHERE INET_ATON(SUBSTRING_INDEX(CONCAT(version, '.0.0.0'), '.', 4)) > INET_ATON(SUBSTRING_INDEX(CONCAT('2.1.27', '.0.0.0'), '.', 4));

Order By example:

SELECT * FROM versions
ORDER BY INET_ATON(SUBSTRING_INDEX(CONCAT(version, '.0.0.0'), '.', 4));

Maybe you can use INET6_ATON for covering versions that has hexadecimal characters (a-f)?

If all your version numbers look like any of these:

X
X.X
X.X.X
X.X.X.X

where X is an integer from 0 to 255 (inclusive), then you could use the INET_ATON() function to transform the strings into integers fit for comparison.

Before you apply the function, though, you'll need to make sure the function's argument is of the X.X.X.X form by appending the necessary quantity of '.0' to it. To do that, you will first need to find out how many .'s the string already contains, which can be done like this:

CHAR_LENGTH(ver) - CHAR_LENGTH(REPLACE(ver, '.', '')

That is, the number of periods in the string is the length of the string minus its length after removing the periods.

The obtained result should then be subtracted from 3 and, along with '.0', passed to the REPEAT() function:

REPEAT('.0', 3 - CHAR_LENGTH(ver) + CHAR_LENGTH(REPLACE(ver, '.', ''))

This will give us the substring that must be appended to the original ver value, to conform with the X.X.X.X format. So, it will, in its turn, be passed to the CONCAT() function along with ver. And the result of that CONCAT() can now be directly passed to INET_ATON(). So here's what we get eventually:

INET_ATON(
  CONCAT(
    ver,
    REPEAT(
      '.0',
      3 - CHAR_LENGTH(ver) + CHAR_LENGTH(REPLACE(ver, '.', ''))
    )
  )
)

And this is only for one value! :) A similar expression should be constructed for the other string, afterwards you can compare the results.

References:

• INET_ATON()

• CHAR_LENGTH()

• CONCAT()

• REPEAT()

• REPLACE()

Finally, I found another way to sort version strings.

I just justify the string before storing into de database in a way it is sortable. As I am using the python Django framework, I just have created a VersionField that 'encode' the version string while storing and 'decode' it while reading, so that it is totally transparent for the application :

Here my code :

The justify function :

def vjust(str,level=5,delim='.',bitsize=6,fillchar=' '):
    """
    1.12 becomes : 1.    12
    1.1  becomes : 1.     1
    """
    nb = str.count(delim)
    if nb < level:
        str += (level-nb) * delim
    return delim.join([ v.rjust(bitsize,fillchar) for v in str.split(delim)[:level+1] ])

The django VersionField :

class VersionField(models.CharField) :

    description = 'Field to store version strings ("a.b.c.d") in a way it is sortable'

    __metaclass__ = models.SubfieldBase

    def get_prep_value(self, value):
        return vjust(value,fillchar=' ')

    def to_python(self, value):
        return re.sub('\.+$','',value.replace(' ',''))