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

Asked 2012-01-03T08:08:31.750

I have firmware version strings into my table (like "4.2.2" or "4.2.16")

How can I compare, select or sort them ?

I cannot use standard strings comparison : "4.2.2" is a seen by SQL greater than "4.2.16"

As version strings, I would like 4.2.16 to be greater than 4.2.2

I would like to consider that firmware version can have chars in them : 4.24a1, 4.25b3 ... for this, usually, the subfield with chars has a fixed length.

how to proceed ?

Edit Report

2 Answers

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:

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:

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

answered 2012-01-03T11:13:25.767

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

2 Answers

Your Answer