Compare software version numbers - which is higher?

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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  
  

Answer 4

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.

Answer 5

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