SQL Server Software Version Number for string comparison and Comparison

In SQL Server, the software version number is compared to the character string. in SQL, the version numbers such as 1.2.1.571.2.12.57 are compared directly. In this case, a downconversion is required to convert 1.2.1.57 to 00001000020000100057 for comparison. [fun_split_version] is used for comparison. createfunction [dbo]. [fun_spl

In SQL Server, the software version number is compared to the character string. in SQL, the version number 1.2.1.57 1.2.12.57 is compared directly. In this case, a downconversion is required to convert 1.2.1.57 to 00001000020000100057 for comparison. [fun_split_version] create function [dbo] for conversion. [fun_spl

SQL Server Software Version Number for string comparison and Comparison

In SQL, it is problematic to directly compare version numbers such as 1.2.1.57 1.2.12.57.

Downstream conversion is required

Convert 1.2.1.57 to 00001000020000100057 for comparison

[Fun_split_version] used for conversion
Create function [dbo]. [fun_split_version]
(
@ Version varchar (50) -- version number
)
Returns decimal (20, 0)
As
Begin
-- Select [dbo]. [fun_split_version] ('2017. 23344.34234.53441 ');
-- Declare @ version varchar (50)
-- Set @ version = '12. 2344.34234.53441'
Declare @ return decimal (20, 0)
Declare @ v_1 varchar (20)
Declare @ v_2 varchar (20)
Declare @ v_3 varchar (20)
Declare @ v_4 varchar (20)
Declare @ wz1 int, @ wz2 int, @ wz3 int, @ wz4 int
Select @ wz1 = charindex ('.', @ version), @ wz2 = charindex ('.', @ version, CHARINDEX ('.', @ version) + 1)
, @ Wz3 = charindex ('.', @ version, charindex ('.', @ version, CHARINDEX ('.', @ version) + 1) + 1)

Set @ v_1 = right ('20140901' + substring (@ version, 0, @ wz1), 5)
Set @ v_2 = right ('000000' + substring (@ version, @ wz1 + 1, @ wz2-@ wz1-1), 5)
Set @ v_3 = right ('000000' + substring (@ version, @ wz2 + 1, @ wz3-@ wz2-1), 5)
Set @ v_4 = right ('000000' + substring (@ version, @ wz3 + 1, len (@ version)-@ wz3), 5)

-- Select @ v_1, @ v_2, @ v_3, @ v_4

Set @ return = convert (decimal (20, 0), @ v_1 + @ v_2 + @ v_3 + @ v_4)
Return @ return
End

Posted on

, Virtual host, virtual host, website Space