MySQL query - comparing version numbers

Question

I have a field in one of my SQL tables that stores the version number, for example, '3.4.23'or '1.224.43'.

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

SELECT * FROM versions WHERE version_number > '2.1.27'

Answer 1

Thanks for the advice from @symcbean and @ gordon-linoff, my last request 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.

This will convert this:

X.XX.XXX > 2.1.27

to that:

'000000000X00000000XX0000000XXX' > '000000000200000000010000000027'

Answer 2

, , ? f (x) f (y) . , , , 4 , , , 0 ( Mariadb, ), , . 2.1.27 "000200010027".

, ​​ . , 3 .

, , 3 , 256, ...

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

Answer 3

With a few conditions, this is a bit of a pain. Here is 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 expression substring_index()can be used on the right side, but using constants makes it easier to look at the logic.

Answer 4

@Adam. If there are short version numbers, such as v1 or v1.2, it is better to concat version_numberas CONCAT(version_number, '.0.0')first

Answer 5

Any ideas how to implement the same in sqlite? sqlite does not provide LPAD and substring_index