Kermit - 1 year ago 50
SQL Question

# How to compare software versions using SQL Server?

When trying to compare software versions 5.12 to 5.8, version 5.12 is newer, however mathematically 5.12 is less than 5.8. How would I compare the two versions so that a newer version returns 'Y'?

``````SELECT CASE WHEN 5.12 > 5.8 THEN 'Y' ELSE 'N' END
``````

Possible Solutions

1. Add a 0 after the decimal in 5.8 so that it compares 5.08 to 5.12, however it seems like this would require a bit of code.

2. Simply compare values after the decimal (ie. 12 > 8), however this fails when the version rolls to 6.0.

3. Use reverse logic and assume that if 5.12 is less than 5.8 to return 'Y'. I believe this would fail when the version rolls to 6.0.

``````declare @v1 varchar(100) = '5.12'
declare @v2 varchar(100) = '5.8'

select
case
when CONVERT(int, LEFT(@v1, CHARINDEX('.', @v1)-1)) < CONVERT(int, LEFT(@v2, CHARINDEX('.', @v2)-1)) then 'v2 is newer'
when CONVERT(int, LEFT(@v1, CHARINDEX('.', @v1)-1)) > CONVERT(int, LEFT(@v2, CHARINDEX('.', @v2)-1)) then 'v1 is newer'
when CONVERT(int, substring(@v1, CHARINDEX('.', @v1)+1, LEN(@v1))) < CONVERT(int, substring(@v2, CHARINDEX('.', @v2)+1, LEN(@v1))) then 'v2 is newer'
when CONVERT(int, substring(@v1, CHARINDEX('.', @v1)+1, LEN(@v1))) > CONVERT(int, substring(@v2, CHARINDEX('.', @v2)+1, LEN(@v1))) then 'v1 is newer'
else 'same!'

end
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download