HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Easiest way to find the larger version number?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
easiestthenumberlargerversionwayfind

Problem

I have a table that stores version information in multi-dotted strings, ie '4.1.345.08', '5.0.1.100', etc. I am looking for the quickest way to find out which is larger. Is there a faster, or easier way on the eyes than what I have come up with below? One caveat is that I am 90% sure that we should ONLY find dotted numeric values here, but I can't guarantee that in the future.

DECLARE @str1 nvarchar(max)='15.0.4000.8'
DECLARE @str2 nvarchar(max)='15.6.4000.8'

SELECT 
CASE 
WHEN PARSENAME(@str1, 4) > PARSENAME(@str2, 4) 
   THEN @str1
WHEN PARSENAME(@str1, 4)  PARSENAME(@str2, 3)
      THEN @str1
   WHEN PARSENAME(@str1, 3)  PARSENAME(@str2, 2)
         THEN @str1
      WHEN PARSENAME(@str1, 2)  PARSENAME(@str2, 1)
            THEN @str1
         WHEN PARSENAME(@str1, 1) < PARSENAME(@str2, 1)
            THEN @str2
         ELSE NULL
         END
      END
   END
END

Solution

A little easier on the eyes perhaps. It relies on the inferences I can make from your sample data (that three of the octets are 1000) - you may need to make adjustments to the multipliers to reflect any differences in real data. I doubt performance will be any different, but depending on how often you are running these comparisons, you may consider computed/persisted columns storing the calculations.

DECLARE @str1 nvarchar(max)='15.0.4000.8',
        @str2 nvarchar(max)='15.6.4000.8';

DECLARE @i1 BIGINT = 10000000000 * PARSENAME(@str1, 4)
    + 1000000 * PARSENAME(@str1, 3)
    + 10 * PARSENAME(@str1,2) 
    + PARSENAME(@str1, 1),
  @i2 BIGINT = 10000000000 * PARSENAME(@str2, 4)
    + 1000000 * PARSENAME(@str2, 3)
    + 10 * PARSENAME(@str2,2) 
    + PARSENAME(@str2, 1);

SELECT @i1, @i2, CASE WHEN @i1 > @i2 THEN @i1 ELSE @i2 END;


Results:

150000040008    150006040008    150006040008

Code Snippets

DECLARE @str1 nvarchar(max)='15.0.4000.8',
        @str2 nvarchar(max)='15.6.4000.8';

DECLARE @i1 BIGINT = 10000000000 * PARSENAME(@str1, 4)
    + 1000000 * PARSENAME(@str1, 3)
    + 10 * PARSENAME(@str1,2) 
    + PARSENAME(@str1, 1),
  @i2 BIGINT = 10000000000 * PARSENAME(@str2, 4)
    + 1000000 * PARSENAME(@str2, 3)
    + 10 * PARSENAME(@str2,2) 
    + PARSENAME(@str2, 1);

SELECT @i1, @i2, CASE WHEN @i1 > @i2 THEN @i1 ELSE @i2 END;
150000040008    150006040008    150006040008

Context

StackExchange Database Administrators Q#124348, answer score: 4

Revisions (0)

No revisions yet.