[Fixed]-How to compare version string ("x.y.z") in MySQL?

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

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:

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

I just use the following which works for all version numbers up to 255:

Compare example:

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

Order By example:

SELECT * FROM versions
ORDER BY INET_ATON(SUBSTRING_INDEX(CONCAT(version, '.0.0.0'), '.', 4));

Maybe you can use INET6_ATON for covering versions that has hexadecimal characters (a-f)?

This is rather a complicated one, as SQL isn’t designed to split out multiple values from a single field – this is a violation of First Normal Form. Assuming that you are not going to have more than three groups of numbers, each of which will not be more than three digits long, try:

cast(substring_index(concat(X,'.0.0.'), '.', 1) as float) * 1000000 +
cast(substring_index(substring_index(concat(X,'.0.0.'), '.', 2), '.', -1) as float) * 1000 +
cast(substring_index(substring_index(concat(X,'.0.0.'), '.', 3), '.', -1) as float)

Python can compare lists element-by-element in exactly the way you want the versions to be compared, so you can simply split on the “.”, call int(x) on each element (with a list comprehension) to convert the string to an int, and then compare

    >>> v1_3 = [ int(x) for x in "1.3".split(".") ]
    >>> v1_2 = [ int(x) for x in "1.2".split(".") ]
    >>> v1_12 = [ int(x) for x in "1.12".split(".") ]
    >>> v1_3_0 = [ int(x) for x in "1.3.0".split(".") ]
    >>> v1_3_1 = [ int(x) for x in "1.3.1".split(".") ]
    >>> v1_3
    [1, 3]
    >>> v1_2
    [1, 2]
    >>> v1_12
    [1, 12]
    >>> v1_3_0
    [1, 3, 0]
    >>> v1_3_1
    [1, 3, 1]
    >>> v1_2 < v1_3
    True
    >>> v1_12 > v1_3
    True
    >>> v1_12 > v1_3_0
    True
    >>> v1_12 > v1_3_1
    True
    >>> v1_3_1 < v1_3
    False
    >>> v1_3_1 < v1_3_0
    False
    >>> v1_3_1 > v1_3_0
    True
    >>> v1_3_1 > v1_12
    False
    >>> v1_3_1 < v1_12
    True
    >>> 

I was searching for the same thing and instead ended up doing this — but staying in mysql :

• Installing this udf library into mysql because I wanted the power of PCRE.

• using this statement

  case when version is null then null
  when '' then 0
  else
  preg_replace( '/[^.]*([^.]{10})[.]+/', '$1', 
      preg_replace('/([^".,\\/_ ()-]+)([".,\\/_ ()-]*)/','000000000$1.',
          preg_replace('/(?<=[0-9])([^".,\\/_ ()0-9-]+)/','.!$1',version
  ))) 
  end
  

I’ll break down what that means:

• preg_replace is a function that the UDF library created. Because it’s a UDF you can just call it from any user or dbspace like that
• ^".,\\/_ () right now i’m considering all of these characters as separators or traditional “dots” in a version
• preg_replace('/(?<=[0-9])([^".,\\/_ ()0-9-]+)/','.!$1',version) means to replace all the non-“dots” and non-numbers that are preceded by a number to be preceded by a “dot” and an exclamation point.
• preg_replace('/([^".,\\/_ ()-]+)([".,\\/_ ()-]*)/','000000000$1.', ...) means to additionally replace all the “dots” with actual dots and to pad all the numbers with 9 zero’s. Also any adjacent dots would be reduced to 1.
• preg_replace( '/0*([^.]{10})[.]+/', '$1', ... ) means to additionally strip all the number blocks down to only 10 digits long and to preserve as many blocks as needed. I wanted to force 6 blocks to keep it under 64-bytes but needing 7 blocks was surprisingly common and thus necessary for my accuracy. Also needed blocks of 10 so 7 blocks of 9 was not an option. But the variable length is working well for me. — remember strings are compared left to right

So now I can handle versions like:

1.2 < 1.10
1.2b < 1.2.0
1.2a < 1.2b
1.2 = 1.2.0
1.020 = 1.20
11.1.1.3.0.100806.0408.000  < 11.1.1.3.0.100806.0408.001
5.03.2600.2180 (xpsp_sp2_rtm.040803-2158)
A.B.C.D = a.B.C.D
A.A  <  A.B

I chose exclamation point because it sorts in the collations sequences (that I’m using anyway) before 0. It’s relative sort to 0 allows letters like b and a when used immediately adjacent to a number above to be treated like a new section and be sort before 0 — which is the padding I am using.

I am using 0 as padding so that vendor’s mistakes like moving from a fixed 3 digit block to a variable one don’t bite me.

You can easily choose more padding if you want to handle silly versions like “2.11.0 Under development (unstable) (2010-03-09)” — the string development is 11 bytes.

You can easily request more blocks in the final replace.

I could have done more but I was trying to do a as few paces as possible with a high-level of accuracy since I have several millions records to scan regularly. If anyone sees an optimization please repsond.

I chose to keep it as a string and not cast into a number because the cast has a cost and also letters are important as we saw. One thing i was thinking about is doing a test on the string and returning an option that isn’t as many passes or less expensive function for tidier cases. like 11.1.1.3 is a very common format

Lots of good solutions here, but I wanted a stored function that would work with ORDER BY

CREATE FUNCTION standardize_version(version VARCHAR(255)) RETURNS varchar(255) CHARSET latin1 DETERMINISTIC NO SQL
BEGIN
  DECLARE tail VARCHAR(255) DEFAULT version;
  DECLARE head, ret VARCHAR(255) DEFAULT NULL;

  WHILE tail IS NOT NULL DO 
    SET head = SUBSTRING_INDEX(tail, '.', 1);
    SET tail = NULLIF(SUBSTRING(tail, LOCATE('.', tail) + 1), tail);
    SET ret = CONCAT_WS('.', ret, CONCAT(REPEAT('0', 3 - LENGTH(CAST(head AS UNSIGNED))), head));
  END WHILE;

  RETURN ret;
END|

to test:

SELECT standardize_version(version) FROM (SELECT '1.2.33.444.5b' AS version UNION SELECT '1' UNION SELECT NULL) AS t;

renders:

00001.00002.00033.00444.00005b
00001
(null)

And allows for comparisons of almost any set of versions, even ones with letters.

/**
function version_compare(version1, version2)

parameters
version1 first version number.
version2 second version number.

return values
-1: if version1 is less than version2;
1: if version1 is greater than version2,
0: if version1 equal version2.

example:
select version_compare('4.2.2','4.2.16') from dual;
version_compare('4.2.2','4.2.16')  
-----------------------------------
    -1 

*/
drop function if exists version_compare;
delimiter @@

create function version_compare(version1 varchar(100), version2 varchar(100))
  returns tinyint
  begin
    declare v_result tinyint;
    declare version1_sub_string varchar(100);
    declare version2_sub_string varchar(100);
    declare version1_sub_int int;
    declare version2_sub_int int;

    declare version1_sub_end tinyint;
    declare version2_sub_end tinyint;


    if version1 = version2 then
      set v_result = 0;
    else

      set version1_sub_string = version1;
      set version2_sub_string = version2;

      lp1 : loop
        set version1_sub_end = locate('.', version1_sub_string);
        set version2_sub_end = locate('.', version2_sub_string);

        if version1_sub_end <> 0 then
          set version1_sub_int = cast(substring(version1_sub_string, 1, version1_sub_end - 1) as signed);
          set version1_sub_string = substring(version1_sub_string, version1_sub_end +1 );
        else
          set version1_sub_int = cast(version1_sub_string as signed);
        end if;

        if version2_sub_end <> 0 then
          set version2_sub_int = cast(substring(version2_sub_string, 1, version2_sub_end - 1) as signed);

          set version2_sub_string = substring(version2_sub_string, version2_sub_end + 1);
        else
          set version2_sub_int = cast(version2_sub_string as signed);
        end if;


        if version1_sub_int > version2_sub_int then
          set v_result = 1;
          leave lp1;

        elseif version1_sub_int < version2_sub_int then
            set v_result = -1;
            leave lp1;
        else
          if version1_sub_end = 0 and version2_sub_end = 0 then
            set v_result = 0;
            leave lp1;

          elseif version1_sub_end = 0 then
              set v_result = -1;
              leave lp1;

          elseif version2_sub_end = 0 then
              set v_result = 1;
              leave lp1;
          end if;      
        end if;

      end loop;
    end if;

    return v_result;

 end@@
delimiter ;

This is my solution. It not depends on number of subversion.

For example:

select SF_OS_VERSION_COMPARE('2016.10.1712.58','2016.9.1712.58');

returns ‘HIGH’

select SF_OS_VERSION_COMPARE('2016.10.1712.58','2016.10.1712.58');

returns ‘EQUAL’

delimiter //

DROP FUNCTION IF EXISTS SF_OS_VERSION_COMPARE //

CREATE FUNCTION SF_OS_VERSION_COMPARE(ver_1 VARCHAR(50), ver_2 VARCHAR(50)) RETURNS VARCHAR(5)
    DETERMINISTIC
    COMMENT 'Return "HIGH", "LOW" OR "EQUAL" comparing VER_1 with VER_2'
BEGIN
    DECLARE v_ver1 VARCHAR(50);
    DECLARE v_ver2 VARCHAR(50);
    DECLARE v_ver1_num INT;
    DECLARE v_ver2_num INT;

    SET v_ver1 = ver_1;
    SET v_ver2 = ver_2;

    WHILE ( v_ver1 <> v_ver2 AND ( v_ver1 IS NOT NULL OR v_ver2 IS NOT NULL )) DO

    SET v_ver1_num = CAST(SUBSTRING_INDEX(v_ver1, '.', 1) AS UNSIGNED INTEGER);
    SET v_ver2_num = CAST(SUBSTRING_INDEX(v_ver2, '.', 1) AS UNSIGNED INTEGER);

    IF ( v_ver1_num > v_ver2_num )
    THEN
        return 'HIGH';
    ELSEIF ( v_ver1_num < v_ver2_num )
    THEN
        RETURN 'LOW';
    ELSE
        SET v_ver1 = SUBSTRING(v_ver1,LOCATE('.', v_ver1)+1);
        SET v_ver2 = SUBSTRING(v_ver2,LOCATE('.', v_ver2)+1);
    END IF;

    END WHILE;

    RETURN 'EQUAL';

END //

I’ve created a flexible SQL-only solution based on the excellent answer of Salman A above:

In this logic, I compare the first 4 version-segments. When the version string has more segments, the tailing ones are ignored.

The code fetches the id and ver columns from a table and then "sanitizes" the ver value to always contain 3 dots – this sanitized version is returned by the sane_ver field.

That sanitized version is then split into 4 integer values, each representing one version segment. You can compare or sort the results based on those 4 integers.

The Code

SELECT
    id,
    ver,
    SUBSTRING_INDEX(sane_ver, '.', 1) + 0 AS ver1,
    SUBSTRING_INDEX(SUBSTRING_INDEX(sane_ver, '.', 2), '.', -1) + 0 AS ver2,
    SUBSTRING_INDEX(SUBSTRING_INDEX(sane_ver, '.', 3), '.', -1) + 0 AS ver3,
    SUBSTRING_INDEX(SUBSTRING_INDEX(sane_ver, '.', 4), '.', -1) + 0 AS ver4
FROM (
    SELECT
        id,
        ver,
        CONCAT(
            ver,
            REPEAT('.0', 3 - CHAR_LENGTH(ver) + CHAR_LENGTH(REPLACE(ver, '.', '')))
        ) AS sane_ver
    FROM (
        SELECT id, ver FROM some_table
    ) AS raw_data 
) AS sane_data

Sample

Here’s a full query with some sample data and a filter that returns only versions that are lower than 1.2.3.4

SELECT
    id,
    ver,
    SUBSTRING_INDEX(sane_ver, '.', 1) + 0 AS ver1,
    SUBSTRING_INDEX(SUBSTRING_INDEX(sane_ver, '.', 2), '.', -1) + 0 AS ver2,
    SUBSTRING_INDEX(SUBSTRING_INDEX(sane_ver, '.', 3), '.', -1) + 0 AS ver3,
    SUBSTRING_INDEX(SUBSTRING_INDEX(sane_ver, '.', 4), '.', -1) + 0 AS ver4
FROM (
    SELECT
        id,
        ver,
        CONCAT(
            ver,
            REPEAT('.0', 3 - CHAR_LENGTH(ver) + CHAR_LENGTH(REPLACE(ver, '.', '')))
        ) AS sane_ver
    FROM (
        SELECT 1 AS id, '1' AS ver UNION
        SELECT 2,  '1.1' UNION
        SELECT 3,  '1.2.3.4.5' UNION
        SELECT 4,  '1.01' UNION
        SELECT 5,  '1.01.03' UNION
        SELECT 6,  '1.01.04a' UNION
        SELECT 7,  '1.01.04' UNION
        SELECT 8,  '1.01.04b' UNION
        SELECT 9,  '1.01.1.9.2.1.0' UNION
        SELECT 10, '1.11' UNION
        SELECT 11, '1.2' UNION
        SELECT 12, '1.2.0' UNION
        SELECT 13, '1.2.1' UNION
        SELECT 14, '1.2.11' UNION
        SELECT 15, '1.2.2' UNION
        SELECT 16, '2.0' UNION
        SELECT 17, '2.0.1' UNION
        SELECT 18, '11.1.1' UNION
        SELECT 19, '2020.11.18.11'
    ) AS raw_data 
) AS sane_data
HAVING 
    ver1 <= 1
    AND (ver2 <= 2 OR ver1 < 1) 
    AND (ver3 <= 3 OR ver2 < 2 OR ver1 < 1) 
    AND (ver4 <  4 OR ver3 < 3 OR ver2 < 2 OR ver1 < 1)

Notes

Note how this logic is different than the original code by Salman A:

The original answer uses CAST AS DECIMAL() which converts 1.02 to 1.020, and 1.1.0 to 1.100
→ That compares 1.02.0 to be lower than 1.1.0 (which is wrong, in my understanding)

The code in this answer converts 1.02 to the integers 1, 2, and 1.1 to the integers 1, 1
→ That compares 1.1.0 to be lower than 1.02.0

Also, both our solutions completely ignore any non-numeric characters, treating 1.2-alpha to be equal to 1.2.0.