SQL sort by version "number", a string of varying length

Question

In PostgreSQL you can do:

SELECT * FROM Requirements
ORDER BY string_to_array(version, '.')::int[];

This last ::int[] makes it convert string values into integers and then compare as such.

For best results, refactor version number storage so that each section has it's own column: MajorVersion, MinorVersion, Revision, Build. Then the ordering problem suddenly becomes trivial. You can also build a computed column for easy retrieval of the full string.

SELECT * FROM Requirements 
WHERE Requirements.Release NOT LIKE '%Obsolete%' 
ORDER BY cast('/' + replace(Requirements.ReqNum , '.', '/') + '/' as hierarchyid);

A slight variation on @vuttipong-l answer (T-SQL)

SELECT VersionNumber
FROM (
SELECT '6.1.3' VersionNumber UNION
SELECT '6.11.3' UNION
SELECT '6.2.3' UNION
SELECT '6.1.12' 
) AS q
ORDER BY cast('/' + VersionNumber + '/' as hierarchyid)

Works in SQL Server starting with 2008, dots are OK in a string representation of a hierarchyid column, so we don't need to replace them with slashes. A quote from the doc:

Comparison is performed by comparing the integer sequences separated by dots in dictionary order.

There's one caveat though: the version segments must not be prefixed with zeroes.

If you are in SQL Server land...

DECLARE @string varchar(40)
SET @string = '1.2.3.4'
SELECT PARSENAME(@string, 1), PARSENAME(@string, 2), PARSENAME(@string, 3), PARSENAME(@string, 4)

Results: 4, 3, 2, 1

Useful for parsing IP Addresses and other dotted items, such as a version number. (You can use REPLACE() to convert items into dotted notation too... e.g. 1-2-3-4 -> 1.2.3.4)