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

Question

I wonder if the SQL geniuses amongst us could lend me a helping hand.

I have a column VersionNo in a table Versions that contains 'version number' values like

VersionNo
---------
1.2.3.1
1.10.3.1
1.4.7.2

etc.

I am looking to sort this, but unfortunately, when I do a standard order by, it is treated as a string, so the order comes out as

VersionNo
---------
1.10.3.1
1.2.3.1
1.4.7.2

Intead of the following, which is what I am after:

VersionNo
---------
1.2.3.1
1.4.7.2
1.10.3.1

So, what I need to do is to sort by the numbers in reverse order (e.g. in a.b.c.d, I need to sort by d,c,b,a to get the correct sort ourder).

But I am stuck as to how to achieve this in a GENERIC way. Sure, I can split the string up using the various sql functions (e.g. left, right, substring, len, charindex), but I can't guarantee that there will always be 4 parts to the version number. I may have a list like this:

VersionNo
---------
1.2.3.1
1.3
1.4.7.2
1.7.1
1.10.3.1
1.16.8.0.1

Can, does anyone have any suggestions? Your help would be much appreciated.

Answer 1

If you can, alter the schema so that the version has 4 columns instead of one. Then sorting is easy.

Answer 2

Depending on SQL engine for MySQL would be sth like this:

SELECT versionNo FROM Versions
ORDER BY
SUBSTRING_INDEX(versionNo, '.', 1) + 0,
SUBSTRING_INDEX(SUBSTRING_INDEX(versionNo, '.', -3), '.', 1) + 0,
SUBSTRING_INDEX(SUBSTRING_INDEX(versionNo, '.', -2), '.', 1) + 0,
SUBSTRING_INDEX(versionNo, '.', -1) + 0;

For MySQL version 3.23.15 an above

SELECT versionNo FROM Versions ORDER BY INET_ATON(ip);

Answer 3

If You are using SQL Server 2008

select VersionNo from Versions order by cast('/' + replace(VersionNo , '.', '/') + '/' as hierarchyid);

What is hierarchyid

Edit:

Solutions for 2000, 2005, 2008: Solutions to T-SQL Sorting Challenge here.

The challenge