Sql2008 a table to save the program version number and EXE files, by contrast to update the version number, several records, from 1.0.0.5 to 1.0.0.12, but I use the select Max (FileVersion) from table_soft version number is 1.0.0.9 removed, rather than 1.0.0.12, I don't want to use the method of removing points, how to solve?

CodePudding user response:

Establish a split function, and then transfer line column, and then sort by column

 CREATE FUNCTION [dbo] [SplitStr] 
(
@ string nvarchar (Max), 
The @ symbol nvarchar (10) 
) 
RETURNS the @ table table (id int identity, value nvarchar (Max)) 
AS 
The begin 
DECLARE @ splitlen int 
The SET @ splitlen=LEN (the @ symbol) - 1 
WHILE CHARINDEX (the @ symbol, @ string) & gt; 0 
The BEGIN 
INSERT the @ table (value) VALUES (LEFT (@ string, CHARINDEX (the @ symbol, @ string) - 1)) 
The SET @ string=STUFF (@ string, 1, CHARINDEX (the @ symbol, @ string) + @ splitlen, ' ') 
END 
INSERT the @ table (value) VALUES (@ string) 
Return 
End 

 declare @ TB table (ver nvarchar (20)) 
Insert into @ TB values (' 1.0.0.5), (' 1.0.0.6), (' 1.0.0.8), (' 1.0.0.9), (' 1.0.0.10), (' 1.0.0.12) 

Select * 
The from @ TB 
Cross the apply (
Select the convert (int, [1]) as v1, convert (int, [2]) as v2, convert (int, [3]) as v3, convert (int, [4]) as the v4 
The from (
Select * from the master. The dbo. SplitStr (ver, '. ') 
) a 
The pivot (Max (value) for id (in [1], [2], [3], [4])) p 
B) 
Desc order by v1 desc, v2, v3 desc, v4 desc 



(6 rows affected) 
Ver v1 v2 v3 v4 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
12 1.0.0.12 1 0 0 
1.0.0.10 1 0 0 10 
1.0.0.9 1 0 0 9 
1.0.0.8 1 0 0 8 
1.0.0.6 1 0 0 0 
1.0.0.5 1 0 0 5 

(6 rows affected) 

CodePudding user response:

Converted to ASCII see can compare

CodePudding user response:

PARSENAME split

CodePudding user response:

Parsename you learn
1. These are decomposed, then stored in a temporary table
2. The temporary table for each column in turn compared to the maximum value (of course, also can let a column (the first column * * 1000 + 10000 + the second column column 3 * 100 + 4 * 10 + 5 columns) together, and then get a maximum value is maximum version number)