question

ruancra avatar image
ruancra asked

SQL function/procedure to compare version numbers.

Hi all Does anyone have a stored procedure or User Defined function that takes 2 input parameters (version numbers) and returns the latest version number? Thanks
stored-procedurestsqlfunctions
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
Can you tell us what format the version number uses. I presume it's not a simple integer or string, as you could just use a comparison operator, or MAX over the values.
1 Like 1 ·

2 Answers

·
ruancra avatar image
ruancra answered
Hi I have found this piece of code that seems to do the trick: SELECT * FROM ( SELECT '2.0.1.4' AS vn union all SELECT '2.0.11.4' AS vn union all SELECT '2.1.1.4' AS vn union all SELECT '2.0.1.0' AS vn ) AS t ORDER BY CAST('/' + t.vn + '/' AS HIERARCHYID) DESC
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Venkataraman avatar image
Venkataraman answered
This article talks about different approaches for comparing software versions: http://stackoverflow.com/questions/11364242/how-to-compare-software-versions-using-sql-server If you go for CLR function, there is a .NET framework class for this. http://msdn.microsoft.com/en-us/library/vstudio/system.version(v=vs.100).aspx
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.