Recently i came across a situation, in which i have to get the MAX version number from a series of versions. Actually, version numbers are accompanied with '.' (dots) in between the different numbers. The normal pattern of a version number is "xx.xx.xx" where "x" represents numeric digits. My objective was to convert this pattern of version into pure numeric form without any dots. You might feel, its pretty simple, just to replace the dots with blank and the version will get converted to number. However, the version number which i was getting was not straight forward. It can be numerous patters, such as
2) xx.x.xx (eg. 10.1.10)
3) xx.xx.x (eg. 10.10.1)
These are some patterns i came across. Here, if we simply replace the dots with blank in pattern no. 2 will always be greater than pattern no. 3.
The solution which i came across is bit tricky.
DECLARE @Version1 varchar(100) = '10.1.10'
DECLARE @Version2 varchar(100) = '10.10.1'
;WITH ConvertVertoNum AS
SELECT Version1 = @Version1,
Version1_Num = CAST(REPLACE(REPLACE(REPLACE(@Version1,
AS BIGINT) ,
Version2 = @Version2,
Version2_Num = CAST(REPLACE(REPLACE(REPLACE(@Version2,
SELECT *, MaxVersion = CASE WHEN Version1_Num > Version2_Num THEN 'Version1' ELSE 'Version2' END
Here, in this solution i am first converting the middle part in the version into two digit number, and the third part again in two digit number. This helps to decide the correct MAX version. Please let me know if you have any easy solution to this problem.