Search code examples
mysqlsqlversioning

MySQL query - compare version numbers

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'

Solution

  • 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'