How to compare software versions using SQL Server?

Question

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.

Solution 1

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

Solution 2

You could use hierarchyid Which you can use by putting a / at the end and start of the string and casting it

e.g.

SELECT CASE WHEN cast('/5.12/' as hierarchyid) > cast('/5.8/' as hierarchyid) THEN 'Y' ELSE 'N' END

That returns a Y

Solution 3

There was a very good solution from a duplicate question here: How to compare SQL strings that hold version numbers like .NET System.Version class?

After playing with the query for a while, I learned that it was not able to compare the last part when there are 4 or more parts (say, if the version number was 1.2.3.4, it would always treat the last one as 0). I have fixed that issue as well as came up with another function to compare two version numbers.

CREATE Function [dbo].[VersionNthPart](@version as nvarchar(max), @part as int) returns int as
Begin

Declare
    @ret as int = null,
    @start as int = 1,
    @end as int = 0,
    @partsFound as int = 0,
    @terminate as bit = 0

  if @version is not null
  Begin
    Set @ret = 0
    while @partsFound < @part
    Begin
      Set @end = charindex('.', @version, @start)
      If @end = 0 -- did not find the dot. Either it was last part or the part was missing.
      begin
        if @part - @partsFound > 1 -- also this isn't the last part so it must bail early.
        begin
            set @terminate = 1
        end
        Set @partsFound = @part
        SET @end = len(@version) + 1; -- get the full length so that it can grab the whole of the final part.
      end
      else
      begin
        SET @partsFound = @partsFound + 1
      end
      If @partsFound = @part and @terminate = 0
      begin
            Set @ret = Convert(int, substring(@version, @start, @end - @start))
      end
      Else
      begin
            Set @start = @end + 1
      end
    End
  End
  return @ret
End
GO

CREATE FUNCTION [dbo].[CompareVersionNumbers]
(
    @Source nvarchar(max),
    @Target nvarchar(max),
    @Parts int = 4
)
RETURNS INT
AS
BEGIN
/*
-1 : target has higher version number (later version)
0 : same
1 : source has higher version number (later version)
*/ 
    DECLARE @ReturnValue as int = 0;
    DECLARE @PartIndex as int = 1;
    DECLARE @SourcePartValue as int = 0;
    DECLARE @TargetPartValue as int = 0;
    WHILE (@PartIndex <= @Parts AND @ReturnValue = 0)
    BEGIN
        SET @SourcePartValue = [dbo].[VersionNthPart](@Source, @PartIndex);
        SET @TargetPartValue = [dbo].[VersionNthPart](@Target, @PartIndex);
        IF @SourcePartValue > @TargetPartValue
            SET @ReturnValue = 1
        ELSE IF @SourcePartValue < @TargetPartValue
            SET @ReturnValue = -1
        SET @PartIndex = @PartIndex + 1;
    END
    RETURN @ReturnValue
END

Usage/Test case:

declare @Source as nvarchar(100) = '4.9.21.018'
declare @Target as nvarchar(100) = '4.9.21.180'
SELECT [dbo].[CompareVersionNumbers](@Source, @Target, DEFAULT) -- default version parts are 4

SET @Source = '1.0.4.1'
SET @Target = '1.0.1.8'
SELECT [dbo].[CompareVersionNumbers](@Source, @Target, 4) -- typing out # of version parts also works

SELECT [dbo].[CompareVersionNumbers](@Source, @Target, 2) -- comparing only 2 parts should be the same

SET @Target = '1.0.4.1.5'
SELECT [dbo].[CompareVersionNumbers](@Source, @Target, 4) -- only comparing up to parts 4 so they are the same
SELECT [dbo].[CompareVersionNumbers](@Source, @Target, 5) -- now comparing 5th part which should indicate that the target has higher version number

Solution 4

I recommend to create a SQL CLR function:

public partial class UserDefinedFunctions
{
    [SqlFunction(Name = "CompareVersion")] 
    public static bool CompareVersion(SqlString x, SqlString y)
    {
        return Version.Parse(x) > Version.Parse(y);
    }
}

Notes:

• SqlString has explicit cast to string.
• Pass full version string as of a.b.c.d

Solution 5

I encountered this when trying to filter SQL rows based on semantic versioning. My solution was a bit different, in that I wanted to store configuration rows tagged with a semantic version number and then select rows compatible with a running version of our software.

Assumptions:

• My software will include a configuration setting containing the current version number
• Data-driven configuration rows will include a min version number
• I need to be able to select configuration rows where min <= current.

Examples:

• Version 1.0.0 should include: 1.0.0, 1.0.0-*, 1.0.0-beta.1
• Version 1.0.0 should exclude: 1.0.1, 1.1.0, 2.0.0
• Version 1.1.0-beta.2 should include: 1.0.0, 1.0.1, 1.1.0-beta.1, 1.1.0-beta.2
• Version 1.1.0-beta.2 should exclude: 1.1.0, 1.1.1, 1.2.0, 2.0.0, 1.1.1-beta.1

The MSSQL UDF is:

CREATE FUNCTION [dbo].[SemanticVersion] (
    @Version nvarchar(50)
)
RETURNS nvarchar(255)

AS
BEGIN

    DECLARE @hyphen int = CHARINDEX('-', @version)
    SET @Version = REPLACE(@Version, '*', ' ')
    DECLARE 
        @left nvarchar(50) = CASE @hyphen WHEN 0 THEN @version ELSE SUBSTRING(@version, 1, @hyphen-1) END,
        @right nvarchar(50) = CASE @hyphen WHEN 0 THEN NULL ELSE SUBSTRING(@version, @hyphen+1, 50) END,
        @normalized nvarchar(255) = '',
        @buffer int = 8

    WHILE CHARINDEX('.', @left) > 0 BEGIN
        SET @normalized = @normalized + CASE ISNUMERIC(LEFT(@left, CHARINDEX('.', @left)-1))
            WHEN 0 THEN LEFT(@left, CHARINDEX('.', @left)-1)
            WHEN 1 THEN REPLACE(STR(LEFT(@left, CHARINDEX('.', @left)-1), @buffer), SPACE(1), '0')
        END  + '.'
        SET @left = SUBSTRING(@left, CHARINDEX('.', @left)+1, 50)
    END
    SET @normalized = @normalized + CASE ISNUMERIC(@left)
        WHEN 0 THEN @left
        WHEN 1 THEN REPLACE(STR(@left, @buffer), SPACE(1), '0')
    END

    SET @normalized = @normalized + '-'
    IF (@right IS NOT NULL) BEGIN
        WHILE CHARINDEX('.', @right) > 0 BEGIN
            SET @normalized = @normalized + CASE ISNUMERIC(LEFT(@right, CHARINDEX('.', @right)-1))
                WHEN 0 THEN LEFT(@right, CHARINDEX('.', @right)-1)
                WHEN 1 THEN REPLACE(STR(LEFT(@right, CHARINDEX('.', @right)-1), @buffer), SPACE(1), '0')
            END  + '.'
            SET @right = SUBSTRING(@right, CHARINDEX('.', @right)+1, 50)
        END
        SET @normalized = @normalized + CASE ISNUMERIC(@right)
            WHEN 0 THEN @right
            WHEN 1 THEN REPLACE(STR(@right, @buffer), SPACE(1), '0')
        END
    END ELSE 
        SET @normalized = @normalized + 'zzzzzzzzzz'

    RETURN @normalized

END

SQL tests include:

SELECT CASE WHEN dbo.SemanticVersion('1.0.0-alpha') < dbo.SemanticVersion('1.0.0-alpha.1') THEN 'Success' ELSE 'Failure' END
SELECT CASE WHEN dbo.SemanticVersion('1.0.0-alpha.1') < dbo.SemanticVersion('1.0.0-alpha.beta') THEN 'Success' ELSE 'Failure' END
SELECT CASE WHEN dbo.SemanticVersion('1.0.0-alpha.beta') < dbo.SemanticVersion('1.0.0-beta') THEN 'Success' ELSE 'Failure' END
SELECT CASE WHEN dbo.SemanticVersion('1.0.0-beta') < dbo.SemanticVersion('1.0.0-beta.2') THEN 'Success' ELSE 'Failure' END
SELECT CASE WHEN dbo.SemanticVersion('1.0.0-beta.2') < dbo.SemanticVersion('1.0.0-beta.11') THEN 'Success' ELSE 'Failure' END
SELECT CASE WHEN dbo.SemanticVersion('1.0.0-beta.11') < dbo.SemanticVersion('1.0.0-rc.1') THEN 'Success' ELSE 'Failure' END
SELECT CASE WHEN dbo.SemanticVersion('1.0.0-rc.1') < dbo.SemanticVersion('1.0.0') THEN 'Success' ELSE 'Failure' END


SELECT CASE WHEN dbo.SemanticVersion('1.0.0-*') <= dbo.SemanticVersion('1.0.0') THEN 'Success' ELSE 'Failure' END
SELECT CASE WHEN dbo.SemanticVersion('1.0.*') <= dbo.SemanticVersion('1.0.0') THEN 'Success' ELSE 'Failure' END
SELECT CASE WHEN dbo.SemanticVersion('1.*') <= dbo.SemanticVersion('1.0.0') THEN 'Success' ELSE 'Failure' END
SELECT CASE WHEN dbo.SemanticVersion('*') <= dbo.SemanticVersion('1.0.0') THEN 'Success' ELSE 'Failure' END

SELECT CASE WHEN dbo.SemanticVersion('1.0.0-*') <= dbo.SemanticVersion('1.0.0') THEN 'Success' ELSE 'Failure' END
SELECT CASE WHEN dbo.SemanticVersion('1.0.1-*') > dbo.SemanticVersion('1.0.0') THEN 'Success' ELSE 'Failure' END
SELECT CASE WHEN dbo.SemanticVersion('1.0.1-*') <= dbo.SemanticVersion('1.0.1') THEN 'Success' ELSE 'Failure' END
SELECT CASE WHEN dbo.SemanticVersion('1.1.*') > dbo.SemanticVersion('1.0.9') THEN 'Success' ELSE 'Failure' END
SELECT CASE WHEN dbo.SemanticVersion('1.1.*') <= dbo.SemanticVersion('1.2.0') THEN 'Success' ELSE 'Failure' END
SELECT CASE WHEN dbo.SemanticVersion('1.*') <= dbo.SemanticVersion('2.0.0') THEN 'Success' ELSE 'Failure' END
SELECT CASE WHEN dbo.SemanticVersion('1.*') > dbo.SemanticVersion('0.9.9-beta-219') THEN 'Success' ELSE 'Failure' END
SELECT CASE WHEN dbo.SemanticVersion('*') <= dbo.SemanticVersion('0.0.1-alpha-1') THEN 'Success' ELSE 'Failure' END

View more solutions

Comments

[Kermit]

Can you elaborate what you mean by "Do not store in a string what is not a string?"

[TomTom]

Simple 5.12 is NOT A STRING - it is a sequence of 2 numbers (5, 12). Stroring them in a varchar is forcing string semantics on that - which do not work. Storing them in a custom type means you can put proper semantics in. msdn.microsoft.com/en-us/library/ms131120.aspx has details about that.

[Kermit]

Perhaps I should have added that I am storing these as decimals.

[TomTom]

As bad - they are NOT DECIMALS either, so you (a) waste space and (b) impose a wrong semantics over them.

[Kermit]

I'm not sure if I'm not testing this correctly, but it seems to fail when @var = 5.14 and @var2 = 5.8.

[praveen]

I haven't executed the query but you can convert parsename to int example cast(PARSENAME(@var,1) as int .I have updated my answer !!

[Kermit]

When data type is float, it does not work when the version ends in .0.

[Kermit]

The current version number was compared to the version in a row, which was stored as a decimal.

[DaveE]

And this can easily be extended out to n-part version numbers as necessary.

[Ulysses Alves]

This would not work for versions with more than just two digits. E.g: 1.3.0.

[gluttony]

Thanks for you answer, I didn't find a way to easily extend this to n-part then I posted my own answer: stackoverflow.com/a/65698263/11159476 with this you can also for example compare a version "1.2" with "1.2.1" (will return -1, v1 < v2) as "1.2" will be considered as "1.2.0", it is not an usual check but in case during time a digit is added to version a "1.2" will actually be considered equal to "1.2.0".