patternsqlMinor
Easiest way to find the larger version number?
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
ENDSolution
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.
Results:
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 150006040008Code 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 150006040008Context
StackExchange Database Administrators Q#124348, answer score: 4
Revisions (0)
No revisions yet.