Kermit Kermit - 2 months ago 11
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.


Answer
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