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

Question

Possible Duplicate:
How to compare software versions using SQL Server?

I am a complete and utterly new to sql, but today I found an error in a sql query here at work that is very important. So I could use some help:

Given an sql string that represents a product version

'15.0.0.0'

is there a foolproof way to sort or compare that string similar to how .NET class System.Version compares instances?

So, given such a hypothetical construct, or function or what-ever, I would expect that '15.5.568' would be greater than '15.0.0.0'.

Thanks

score 5 · Answer 1 · answered Nov 09 '12 at 21:03

5

Just adding to what @Gordon suggested, here's an example with ParseName

; 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.15.1323.22'
    UNION ALL SELECT '15.15.622.55'
)
SELECT *
FROM
(
    SELECT CAST(PARSENAME(Version, 4) AS INT) AS col1
        , CAST(PARSENAME(Version, 3) AS INT) AS col2
        , CAST(PARSENAME(Version, 2) AS INT) AS col3
        , CAST(PARSENAME(Version, 1) AS INT) AS col4
    FROM tmp
) t0
ORDER BY col1, col2, col3, col4

answered Nov 09 '12 at 21:03

Eli Gassert

9,745
3
30
39

Wow, clever and by var the simplest approach I have found online. Props! – Torre Lasley Jan 08 '16 at 14:01
Note that this code only works if all versions have exactly the same number of parts. – BytesOfMetal Mar 26 '20 at 17:17

score 5 · Accepted Answer · answered Nov 09 '12 at 21:22

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

While I don't understand the function part, the example usage looks straight forward. Thanks very much! — C.J., Nov 09 '12 at 21:37

Gordon Linoff · Answer 3 · 2015-07-20T20:23:40.097

If you have no more than three decimal points, then you can use parsename. The following rectifies the two version numbers to values having 4 characters, so a string comparison owrks. You example would be "0015.0000.0000.0000.0000".

select (case when (right('0000'+coalesce(parsename(v1, 4), '', 4)) +
                   right('0000'+coalesce(parsename(v1, 3), '', 4)) +
                   right('0000'+coalesce(parsename(v1, 2), '', 4)) +
                   right('0000'+coalesce(parsename(v1, 1), '', 4))
                  ) < 
                  (right('0000'+coalesce(parsename(v2, 4), '', 4)) +
                   right('0000'+coalesce(parsename(v2, 3), '', 4)) +
                   right('0000'+coalesce(parsename(v2, 2), '', 4)) +
                   right('0000'+coalesce(parsename(v2, 1), '', 4))
                  )
             then -1
             when v1 = v2
             then 0
             else 1
         end) as Comparison

Note that parsename() only works on up to four-parts in the name.

If you just want to sort, then the following will work:

order by (right('0000'+coalesce(parsename(v1, 4), '', 4)) +
          right('0000'+coalesce(parsename(v1, 3), '', 4)) +
          right('0000'+coalesce(parsename(v1, 2), '', 4)) +
          right('0000'+coalesce(parsename(v1, 1), '', 4))
         )

The `case` expression returns a value of -1 when version v1 is less than version v2, of 0 when they are equal, and 1 when version v1 is greater. My understanding is that you are looking for a way to compare versions. — Gordon Linoff, Nov 09 '12 at 21:41
`right('0000'+coalesce(parsename(v1, 4), 4), '')` should be `right('0000'+coalesce(parsename(v1, 4), ''), 4)` — jo0ls, Jul 20 '15 at 10:46

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

3 Answers3

Linked