0

Simple function for comparing software versions

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

Norbert Krupa

Technical Consultant

Leave a Reply

Your email address will not be published.