Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

I have around 6+ years of experience in SQL Server development and in Business Intelligence area. I am working as Datawarehouse developer. My blog space includes all technical areas related to SQL Server and MSBI (SSIS, SSRS and SSAS).
Browse by Tags · View All
xml 3
dynamic 3
Union All 3
Data flow task 2
Buffer 2
Time 2
Date 2
varchar(max) 1

Archive · View All
February 2013 3
September 2009 3
June 2013 2
January 2013 2
November 2012 2
July 2012 2
July 2009 2
April 2013 1
December 2012 1
February 2010 1

Getting correct MAX version

Dec 20 2012 12:00AM by Divya Agrawal Shah   

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
1) xx.xx.xx
2) xx.x.xx  (eg. 10.1.10)
3) xx.xx.x  (eg. 10.10.1)
4) xx.x.x

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,
									AS BIGINT)		
SELECT *, MaxVersion = CASE WHEN Version1_Num > Version2_Num THEN 'Version1' ELSE 'Version2' END 
FROM ConvertVertoNum

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.

Tags: parsename, max, version, sql

Divya Agrawal Shah
89 · 2% · 629



  • @Divya,

    If PARSENAME() function is sufficient to tokenize your version pattern(less than 5 tokens)

    you can sort your column with versions by each token from left to right in descending order

    and pick the topmost value:

    DECLARE @Version1 varchar(100) ;
    DECLARE @Version2 varchar(100) ;
    set @Version1= '10.1.10';
    set @Version2= '10.10.1';
    WITH Versions(ver)AS
    SELECT  @Version1
    union all
    select @Version2
    SELECT top 1 ver
    FROM Versions
    order by PARSENAME(ver,3)desc,PARSENAME(ver,2)desc,PARSENAME(ver,1) desc;
    commented on Dec 21 2012 2:15PM
    Leonid Koyfman
    50 · 4% · 1183

Your Comment

Sign Up or Login to post a comment.

"Getting correct MAX version" rated 5 out of 5 by 3 readers
Getting correct MAX version , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]