MySQL query - compare version numbers

Question

I have a field in one of my SQL tables which stores a version number, like '3.4.23' or '1.224.43'.

Is there a way to use a greater than condition for this field?

SELECT * FROM versions WHERE version_number > '2.1.27'

You could write a procedure that translates the version number in something comparable and use that WHERE version_normalised(version_number) > version_normalised('2.1.27') — Bart Friederichs
– Bart Friederichs, Commented Jan 19, 2016 at 12:28
How exactly does this work? what is greater than what? is 3.4.23 bigger then 3.224.5? explain your rules. — sagi
– sagi, Commented Jan 19, 2016 at 12:28
Or you create a version comparer: WHERE version_compare(version_number, '2.1.27') Point is, MySQL does not have this built-in. — Bart Friederichs
– Bart Friederichs, Commented Jan 19, 2016 at 12:31
I have tried with MySQL 8.0. In my column, I have a column version with 3 rows 1.2.3, 1.11.1 1.1.1. I can just simply get the version with "SELECT * FROM versions WHERE version > '1.1.1'" Then I can get 1.2.3 and 1.11.1 successfully. Couldn't just use this to do version comparison? — holopekochan
– holopekochan, Commented Oct 19, 2018 at 8:26
@chan3600 will you get 1.11.1 when you query for version > '1.2.1' ? — Adam
– Adam, Commented Oct 19, 2018 at 13:42

Adam · Accepted Answer · 2018-08-16 09:30:35Z

17

Thanks for the tips @symcbean and @gordon-linoff, my final query looks like this:

SELECT *
FROM versions WHERE CONCAT(
        LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(version_number, '.', 1), '.', -1), 10, '0'),
        LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(version_number, '.', 2), '.', -1), 10, '0'),
        LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(version_number, '.', 3), '.', -1), 10, '0') 
       ) > CONCAT(LPAD(2,10,'0'), LPAD(1,10,'0'), LPAD(27,10,'0'));

This allows each component to be up to 10 digits long.

It transforms this:

X.XX.XXX > 2.1.27

to this:

'000000000X00000000XX0000000XXX' > '000000000200000000010000000027'

edited Aug 16, 2018 at 9:30

answered Jan 19, 2016 at 13:00

Adam

5,2933 gold badges33 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Xaver · Accepted Answer · 2020-09-09 08:11:01Z

6

While it would be possible to write a function which would compare version numbers, is this the right way to solve the problem? Comparing f(x) and f(y) cannot be indexed. If you know that any part of the version number will never exceed, say, 4 digits, then you could create an additional field holding the value padded with 0's (or on Mariadb, use a virtual column) which can be indexed, e.g. 2.1.27 would become '000200010027`.

It would be a lot simpler if you stopped trying to use such a numbering schema and just used integers or datetimes. If you must stick with this numbering, then consider splitting the data into 3 columns.

For a quick hack, if you know that the version number will always have 3 component and each component will always be less than 256, then you could...

SELECT * 
FROM versions 
WHERE INET_ATON(CONCAT(version_number, '.0')) > INET_ATON('2.1.27.0');

edited Sep 9, 2020 at 8:11

Xaver

11.9k13 gold badges62 silver badges92 bronze badges

answered Jan 19, 2016 at 12:33

symcbean

48.4k6 gold badges63 silver badges99 bronze badges

5 Comments

Piskvor left the building Over a year ago

Clever use of a IPv4 function; the requirement for "always <256" is however very un-obvious and likely to bite in longer term.

Adam Over a year ago

Thanks @symcbean for the inspiration, I can't the INET_ATON, since my version numbers can be greater than 256, but I used your first idea, and you can see my final query in a new answer.

Andre Figueiredo Over a year ago

unless I'm missing something, don't need to pad if you are comparing same amount of version parts.. ie INET_ATON('1.0.0') is the same INET_ATON('0.1.0.0').

symcbean Over a year ago

Padding is a different approach than using inet numbers

Andre Figueiredo Over a year ago

sorry, I mean padding right 0, concatenating with '0' on both side of comparison in last proposed solution. It'd just a hack anyway. I agree w you that it shouldn't be used, preferring a different approach on storing data.

Gordon Linoff · Accepted Answer · 2016-01-19 12:30:34Z

1

With multiple conditions, this is a bit of a pain. Here is a brute force approach:

where substring_index(version_number, '.', 1) + 0 > 2 or
      (substring_index(version_number, '.', 1) = '2' and
       substring_index(version_number, '.', 2) + 0 > 2.1
      ) or
      (substring_index(version_number, '.', 2) = '2.1' and
       substring_index(version_number, '.', -1) + 0 > 27
      )

Note: the same substring_index() expression can be used on the right-hand side, but using constants makes it simpler to see the logic.

answered Jan 19, 2016 at 12:30

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Wenpin CHOU · Accepted Answer · 2018-04-11 02:48:48Z

0

@Adam In case there are short version numbers like v1 or v1.2, better concat version_number like CONCAT(version_number, '.0.0') first

answered Apr 11, 2018 at 2:48

Wenpin CHOU

4534 silver badges7 bronze badges

Comments

tim · Accepted Answer · 2021-09-13 15:37:42Z

0

I use the following which works for any version numbers less than 255:

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

answered Sep 13, 2021 at 15:37

tim

2,7363 gold badges33 silver badges49 bronze badges

Collectives™ on Stack Overflow

MySQL query - compare version numbers

5 Answers 5

Comments

5 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

5 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related