how to manipulate string column of numbers separated by dots using t-sql

Question

I have a column of nvarchar(255) type that represents software version numbers:

  VersionNumber
 ---------------

    1.0.0.505
    1.0.0.506
    1.0.0.507
    1.0.0.508
    1.0.0.509
    1.0.1.2

I need to extract the maximum version number (the min version number in the example above is 1.0.0.505 and the max version number is 1.0.1.2, values arranged from the smallest to the highest).

in order to explain exactly what i need - if i could use imperative programming language i think i would do something like that to detect the max version number:

lets say version number is d.c.b.a.
i would separate each version number to four different variables: a b c d
that i will sum each series.
a will be summed by tens
b will be summed by hundreds
c will be summed by thousands
d will be summed by milions

than the maximum total sum of each Max(a+b+c+d) will be the max version.
but what is the technic to achieve something like that in sql?

HIERARCHYID can help, https://stackoverflow.com/questions/3474870/how-can-i-sort-a-version-number-column-generically-using-a-sql-server-query — Alex K., Dec 07 '17 at 18:27
@AlexK. wow! i have tried a lot to search before asking and did not found it , that is exactly what i need. thank you. — Jonathan Applebaum, Dec 07 '17 at 18:32
for future readers: based on @AlexK. link that is the solution: `select TOP 1 VersionNumber from Users order by (cast('/' + replace(VersionNumber , '.', '/') + '/' as hierarchyid)) DESC;` — Jonathan Applebaum, Dec 07 '17 at 18:42
You can post that as an answer & accept it to close this question. — Alex K., Dec 07 '17 at 18:43
Why not `MAX(VersionNumber)`? Under what circumstances does that not get you the correct result? — Alan Burstein, Dec 07 '17 at 18:49
@AlanBurstein because its `varchar` column I did not even think of trying it, i see its not throwing an exception...but are you sure it will return the correct result under all conditions? — Jonathan Applebaum, Dec 07 '17 at 18:55
@DhruvJoshi i have tried your solution, its working, very creative to do that imperaive code with sql, thank you for your time and effort!! +1 — Jonathan Applebaum, Dec 07 '17 at 20:50

score 1 · Accepted Answer · answered Dec 07 '17 at 18:44

1

for future readers: based on @AlexK. link that is the solution:

select TOP 1 VersionNumber from Users order by (cast('/' + replace(VersionNumber , '.', '/') + '/' as hierarchyid)) DESC;

answered Dec 07 '17 at 18:44

Jonathan Applebaum

5,738
4
33
52

score 0 · Answer 2 · answered Dec 07 '17 at 18:32

0

try this

select max(replace(version,'.','')) from yourtable

answered Dec 07 '17 at 18:32

ali zarei

1,212
11
17

How would that rank `1.23.4.5` versus `1.2.34.5`? – HABO Dec 07 '17 at 18:56

how to manipulate string column of numbers separated by dots using t-sql

2 Answers2