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