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

Question

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 ?

Answer 1

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()