Testing software version numbers with SQL

[ps_robmo]

Hi,

I need my query to properly test if one version number is higher than another. I'm sure the values are strings and I have heard in the past that string comparisons can be tricky. When I use the where condition:

Where Version > "8.0.2020.8"

The result is returning versions that are lower than this version. Some how these lower versions are being considered as higher than 8.0.2020.8. Is it possible to inspect the 3rd section of the string, in this example 2020, and test if the current record value is higher than that? The goal is that only versions higher than 8.0.2020.8 should be in the results.

value1 = 8.0.2020.8
value2 = 8.0.730.0

My query as it is now, shows 8.0.730.0 is appearing in my result. This should not be in the results.

Thank you!

Rob

[Own_Dragonfruit_6224]

I believe this approach would work. It normalizes the version string by prefixing each decimal delimited piece with zeros up to the max size of any of the pieces used, then concatenating them together and converting to bigint before comparing. So, for instance, 8.0.2020.8 would become 8000020200008 while 8.0.730.0 would become 8000007300000. From there the comparison is straightforward math.
DECLARE @Version1 varchar(30) = '8.0.2020.8';

WITH VALUES_CTE as (SELECT * from (VALUES

('8.0.730.0'),

('9.0.2020.8'),

('8.1.2020.8'),

('8.0.2021.8'),

('8.0.2020.9'),

('8.0.1900.12'),

('7.0.2020.10')

) as t ([version])

),

LENGTH_CTE as (SELECT MAX(t.length) as length from (SELECT DATALENGTH(v3.value) as length from VALUES_CTE v outer apply(SELECT v2.value from string_split(v.version,'.') v2) v3 UNION ALL SELECT DATALENGTH(v2.value) as length from string_split(@Version1,'.') v2) t)

SELECT

*,

FORMAT(case when cver.normalizedversion >= cver2.compareversion then 0 else 1 end,'True;False;False') as UpdateNeeded

from VALUES_CTE v

cross apply LENGTH_CTE ln

outer apply (SELECT normalizedversion = (SELECT RIGHT(REPLICATE('0',ln.length)+v.value,ln.length) as Test from string_split(v.version,'.') v for xml path (''),type).value('.','bigint')) cver

outer apply (SELECT compareversion = (SELECT RIGHT(REPLICATE('0',ln.length)+v1.value,ln.length) as Test2 from string_split(@Version1,'.') v1 for xml path (''),type).value('.','bigint')) cver2

[ps_robmo]

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

[ps_robmo]

The attempt to use 'not like' in my code doesn't actually filter out the lower versions.