Home how to manipulate string column of numbers separated by dots using t-sql
Reply: 2

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

jonathana
1#
jonathana Published in 2017-12-07 18:24:15Z

This question already has an answer here:

  • How Can I Sort A 'Version Number' Column Generically Using a SQL Server Query 4 answers

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?

ali zarei
2#
ali zarei Reply to 2017-12-07 18:32:42Z

try this

select max(replace(version,'.','')) from yourtable
jonathana
3#
jonathana Reply to 2017-12-07 18:44:23Z

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;

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.330313 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO