Compare software version numbers - which is higher?

Rob M 61 Reputation points
2022-08-24T18:39:23.93+00:00

Hi,

I need a query that is able to properly test if one version number is higher than another. When I use the where condition:

Version > "8.0.2020.8"

The result is returning versions that are lower than this version. Is it possible to inspect the 3rd section, in this example 2020, and compare that part against another version number?

value1 = 8.0.2020.8
value2 = 8.0.730.0

When these are compared as strings, 8.0.730.0 is appearing in my result.

Thank you!
Rob

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,565 questions
{count} votes

6 answers

Sort by: Most helpful
  1. Michael Taylor 49,251 Reputation points
    2022-08-24T20:49:57.513+00:00

    SQL is going to do string comparison because you have strings. What you're asking for is integral comparison against 4 different version #s. The only way to do this in SQL is to pick the string apart and compare the values separately as string comparison will never work properly based upon character ordinals.

    You didn't specify what version of SQL you're using so your options are limited to what SQL supports. If you're using SQL 2017+ then it has the necessary string functions to do what you want relatively cleanly. Basically you need to split the string on the dots, convert the string tokens to numbers that are padded longer than any reasonable value (version #s are 64K values generally) and then put them back together.

       SELECT STRING_AGG(FORMAT(CONVERT(INT, value), '000000'), '.') FROM STRING_SPLIT(@version, '.')  
    

    The resulting string is now something like 000008.000000.000730.000000. However now when you do an ordinal comparison of, say, 000730 and 002020 then the values will compare properly.

    If you are running SQL 2016 or older then STRING_AGG is not available and you'll have to revert to an old school approach to get it to work. Here's one discussion on the old forums on how you might do it.

    1 person found this answer helpful.

  2. Tom Phillips 17,716 Reputation points
    2022-08-24T21:03:35.057+00:00

    SQL version numbers are in sections separated by period, {major version}.{minor version}.{build ver}.{patch level}.

    The simplest way to compare them is to separate the sections as cooldadtx said.

    I hope you are not still using SQL 2000 and that is just an example string.

    1 person found this answer helpful.
    0 comments No comments

  3. Erland Sommarskog 102.4K Reputation points
    2022-08-24T21:38:28.61+00:00

    Here is a solution which works the assumption that

    • There are not more than four components in the version numbers.
    • There are the same number of components on both sides.
    • Separator is always dot.
       DECLARE @value1 varchar(20) = '8.0.2020.8',  
               @value2 varchar(20) = '8.0.730.0'  
      
       SELECT CASE WHEN convert(int, parsename(@value1, 4)) > convert(int, parsename(@value2, 4)) THEN 'greater'  
                   WHEN convert(int, parsename(@value1, 4)) < convert(int, parsename(@value2, 4)) THEN 'smaller'  
                   ELSE CASE   
                   WHEN convert(int, parsename(@value1, 3)) > convert(int, parsename(@value2, 3)) THEN 'greater'  
                   WHEN convert(int, parsename(@value1, 3)) < convert(int, parsename(@value2, 3)) THEN 'smaller'  
                   ELSE CASE   
                   WHEN convert(int, parsename(@value1, 2)) > convert(int, parsename(@value2, 2)) THEN 'greater'  
                   WHEN convert(int, parsename(@value1, 2)) < convert(int, parsename(@value2, 2)) THEN 'smaller'  
                   ELSE CASE   
                   WHEN convert(int, parsename(@value1, 1)) > convert(int, parsename(@value2, 1)) THEN 'greater'  
                   WHEN convert(int, parsename(@value1, 1)) < convert(int, parsename(@value2, 1)) THEN 'smaller'  
                   ELSE 'equal'  
              END END END END  
      
    1 person found this answer helpful.
    0 comments No comments

  4. CosmogHong-MSFT 23,951 Reputation points Microsoft Vendor
    2022-08-25T07:45:45.703+00:00

    Hi @Rob M
    How about creating a UDF to add the different parts of version strings and return a BIGINT number, then you can apply this UDF in your filter clause to compare.
    And, if your Version strings always have 3 dots(which means 4 differnt parts), then you could consider using Parsename function to split the version value by '.'
    Check this sample:

    IF OBJECT_ID('dbo.fn_Calculate_Version')IS NOT NULL   
    DROP FUNCTION dbo.fn_Calculate_Version;  
    GO   
      
    CREATE FUNCTION dbo.fn_Calculate_Version(@VersionValue VARCHAR(50))  
    RETURNS BIGINT  
    AS   
    BEGIN  
      DECLARE @Calculate_Version AS BIGINT;  
      SET @Calculate_Version =   
             Right('0000'+Coalesce(Parsename(@VersionValue, 4),''), 4) +  
             Right('0000'+Coalesce(Parsename(@VersionValue, 3),''), 4) +  
             Right('0000'+Coalesce(Parsename(@VersionValue, 2),''), 4) +  
             Right('0000'+Coalesce(Parsename(@VersionValue, 1),''), 4)  
      RETURN @Calculate_Version;  
    END  
    GO  
      
    CREATE Table #Version (Version VARCHAR(20))  
    INSERT INTO #Version VALUES('1.0.0.5'),('1.5.0.06'),('1.0.0.06'),('8.0.2020.8'),('8.0.730.0'),('8.1.680.7'),('12.15.1323.22'),('12.15.622.55')  
      
    SELECT Version,dbo.fn_Calculate_Version(Version)  
    FROM #Version  
    WHERE dbo.fn_Calculate_Version(Version)>dbo.fn_Calculate_Version('8.0.2020.8')  
    

    Note1: The above solution is based on the premise that the version string has only four parts.
    Note2: If the Max digit of each part is not 4, suppose say 6.
    Then you need to modify the UDF, change Right('0000'+Coalesce(Parsename(@VersionValue, 4),''), 4)+... to Right('000000'+Coalesce(Parsename(@VersionValue, 4),''), 6)+...

    Best regards,
    LiHong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

  5. Rob M 61 Reputation points
    2022-08-24T21:26:26.007+00:00
    select SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.Name, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client from  SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "Java 8 Update%" and SMS_G_System_ADD_REMOVE_PROGRAMS.Version > "8.0.2020.8" and (SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName not like "Update 25" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName not like "Update 40" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName not like "Update 45" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName not like "Update 60" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName not like "Update 66" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName not like "Update 71" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName not like "Update 73" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName not like "Update 91")  
    
    0 comments No comments