Traditional arithmetic would tell you that 5.1 = 5.10. However, arithmetic can’t compare software versions such as 5.1 to 5.10, since 5.10 is the ‘newer’ version. The function below receives two version numbers and returns which of the two is newer (1 or 2). If the versions are the same, the function returns 0. It can handle single decimal software versions such as 1.1.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | CREATE FUNCTION [dbo].[version_compare] ( @v1 varchar(5), @v2 varchar(5) ) RETURNS tinyint AS BEGIN DECLARE @v1_int tinyint, @v1_frc tinyint, @v2_int tinyint, @v2_frc tinyint, @ResultVar tinyint SET @ResultVar = 0 SET @v1_int = CONVERT(tinyint, LEFT(@v1, CHARINDEX('.', @v1) - 1)) SET @v1_frc = CONVERT(tinyint, RIGHT(@v1, LEN(@v1) - CHARINDEX('.', @v1))) SET @v2_int = CONVERT(tinyint, LEFT(@v2, CHARINDEX('.', @v2) - 1)) SET @v2_frc = CONVERT(tinyint, RIGHT(@v2, LEN(@v2) - CHARINDEX('.', @v2))) SELECT @ResultVar = CASE WHEN @v2_int > @v1_int THEN 2 WHEN @v1_int > @v2_int THEN 1 WHEN @v2_frc > @v1_frc THEN 2 WHEN @v1_frc > @v2_frc THEN 1 ELSE 0 END RETURN @ResultVar END GO |
Test results
SELECT [dbo].[version_compare] ('1.1', '0.9')
----
1
SELECT [dbo].[version_compare] ('1.1', '1.1')
----
0
SELECT [dbo].[version_compare] ('1.1', '1.10')
----
2
