How can I select the minimum version (ie x.x.x.x) stored in a varchar column?

Question

I've tried the obvious select min(version) from t but this wouldn't work as the engine considers (and rightly so) that the string '1.12.0.0' is smaller than '1.2.0.0', which is obviously not the case when it comes to version numbers.

I know I can easily split the 4 numbers dynamically (using either computed columns or a view) and then I can just use select top 1 row from myview order by a asc, b asc, c asc, d asc

... but I was curious to know if there was an easier way to achieve this

See also http://stackoverflow.com/questions/2647/split-string-in-sql I guess — Vincent Mimoun-Prat, Dec 14 '12 at 09:56
possible duplicate of [How to compare software versions using SQL Server?](http://stackoverflow.com/questions/11364242/how-to-compare-software-versions-using-sql-server) — Pondlife, Dec 14 '12 at 17:46

Andomar · Answer 1 · 2012-12-14T10:25:13.433

3

If your version numbers contain at most 4 numbers separated by a dot, you can abuse the parsename function:

select  top 1 *
from    Versions
order by
        cast(parsename(Version, 4) as int)
,       cast(parsename(Version, 3) as int)
,       cast(parsename(Version, 2) as int)
,       cast(parsename(Version, 1) as int)

Live example at SQL Fiddle.

edited Dec 14 '12 at 10:25

answered Dec 14 '12 at 10:09

Andomar

216,619
41
352
379

don't you think version 1.2.0.0 is earlier than 1.12.0.0? – Sir Rufo Dec 14 '12 at 10:20
@SirRufo: Good point, the version numbers have to be cast to `int` for sorting. Updated in answer. – Andomar Dec 14 '12 at 10:25

How can I select the minimum version (ie x.x.x.x) stored in a varchar column?

1 Answers1