How to Compare Software Versions Using SQL Server

How to compare software versions using SQL Server?

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

MySQL query - compare version numbers

Thanks for the tips @symcbean and @gordon-linoff, my final query looks like this:

SELECT *
FROM versions WHERE CONCAT(
        LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(version_number, '.', 1), '.', -1), 10, '0'),
        LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(version_number, '.', 2), '.', -1), 10, '0'),
        LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(version_number, '.', 3), '.', -1), 10, '0') 
       ) > CONCAT(LPAD(2,10,'0'), LPAD(1,10,'0'), LPAD(27,10,'0'));

This allows each component to be up to 10 digits long.

It transforms this:

X.XX.XXX > 2.1.27

to this:

'000000000X00000000XX0000000XXX' > '000000000200000000010000000027'

How to compare SQL strings that hold version numbers like .NET System.Version class?

Assuming SQL Server, and a known maximum number of parts, here's a user defined function that does the same as parsename, but works on any number of parts:

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

  if @version is not null
  Begin
    Set @ret = 0
    while @partsFound < @part
    Begin
      Set @end = charindex('.', @version, @start)
      If @end = 0
        Set @partsFound = @part -- bail early
      else
      Begin
        Set @partsFound = @partsFound + 1
        If @partsFound = @part
          Set @ret = Convert(int, substring(@version, @start, @end - @start))
        Else
          Set @start = @end + 1
      End
    End
  End
  return @ret
End

Example Usage:

With
  tmp
As (
    Select '1.0.0.5' As Version
    Union All Select '1.5.0.06'
    Union All Select '1.0.0.06'
    Union All Select '2.0.0.0'
    Union All Select '2.0.1.1'
    Union All Select '15.5.568'
    Union All Select '15.0.0.0'
    Union All Select '15.15.1323.22'
    Union All Select '15.15.622.55'
)

Select
  *
From
   tmp
Order By
  dbo.VersionNthPart(Version, 1),
  dbo.VersionNthPart(Version, 2),
  dbo.VersionNthPart(Version, 3),
  dbo.VersionNthPart(Version, 4)

http://sqlfiddle.com/#!3/e942b/3

How to compare version string ( x.y.z ) in MySQL?

Finally, I found another way to sort version strings.

I just justify the string before storing into de database in a way it is sortable.
As I am using the python Django framework, I just have created a VersionField that 'encode' the version string while storing and 'decode' it while reading, so that it is totally transparent for the application :

Here my code :

The justify function :

def vjust(str,level=5,delim='.',bitsize=6,fillchar=' '):
    """
    1.12 becomes : 1.    12
    1.1  becomes : 1.     1
    """
    nb = str.count(delim)
    if nb < level:
        str += (level-nb) * delim
    return delim.join([ v.rjust(bitsize,fillchar) for v in str.split(delim)[:level+1] ])

The django VersionField :

class VersionField(models.CharField) :

    description = 'Field to store version strings ("a.b.c.d") in a way it is sortable'

    __metaclass__ = models.SubfieldBase

    def get_prep_value(self, value):
        return vjust(value,fillchar=' ')

    def to_python(self, value):
        return re.sub('\.+$','',value.replace(' ',''))

SQL Server Filter by Version

The solution would be to separate the version bits into separate columns for a quick fix you can use the following query

DECLARE @version VARCHAR(100) = '3.12.92';

SELECT  REVERSE(PARSENAME(REVERSE(@version) , 1)) AS FirstBit
       ,REVERSE(PARSENAME(REVERSE(@version) , 2)) AS MiddleBit
       ,REVERSE(PARSENAME(REVERSE(@version) , 3)) AS LastBit 

Result:

FirstBit    MiddleBit    LastBit
   3           12          92

Your Where clause for where first bit = 3 would look something like....

WHERE REVERSE(PARSENAME(REVERSE(@version) , 1))  = '3'

---

Related Topics