Custom Sorting Function - of SQL Server

I would look at using a persisted computed column which processes the string into an int or string or something appropriate for sorting in the native SQL Server sort - i.e.

'1.1.1.1' -> '001.001.001.001'
'10.10.10.10' -> '010.010.010.010'
'1.10.1.10' -> '001.010.001.010'

So that you can sort by the computed column and get expected results.

Alternatively, you can use such an operation inline, but it might be very slow. In addition scalar UDFs are extremely slow.

Creating an SQL CLR function is the way to go. They're extremely fast and powerful. It would be quick and effective as you wouldn't have to change any existing code, and you could specify all the information you need right in your SQL statements.

The SQL CLR function could accept an input string, as well as other parameters specifying which piece of information you'd like to extract from the input string. You could then sort on the return values of the function.

Specifically, I'd create a generic function that accepts three parameters: an input string, a regular expression, and a group name. That function would allow you to pass your database field and a regular expression with named groups right in the SQL statement.

The SQL CLR function would create a Regex, test it against the string, and would ultimately return the matched group's value or null if there was no match or the group was not matched (if the group was optional). Ideally, you'd want to pass the same regular expression to each call (perhaps as a variable like @regex), to take advantage of any CLR caching of the compiled regular expression. The end result would be very flexible and fast.

Regular expression options can be specified inline in the pattern like so: "(?imnsx-imnsx:subexpression)". See: msdn.microsoft.com/en-us/library/yd1hzczs.aspx

The code for such a function would look something like this:

[SqlFunction(IsDeterministic=true,IsPrecise=true,DataAccess=DataAccessKind.None,SystemDataAccess=SystemDataAccessKind.None)]
public static SqlString RegexMatchNamedGroup( SqlChars input, SqlString pattern, SqlString group_name )
    {
        Regex regex = new Regex( pattern.Value );
        Match match = regex.Match( new string( input.Value ) );
        if (!match.Success) //return null if match failed
            return SqlString.Null;
        if (group_name.IsNull) //return entire string if matched when no group name is specified
            return match.Value;
        Group group = match.Groups[group_name.Value];
        if (group.Success)
            return group.Value; //return matched group value
        else
            return SqlString.Null; //return null if named group was not matched         
    }

Your SQL statement could then sort on pieces of your information like so:

declare @regex nvarchar(2000) = '^(?<Major>\d{1,3})\.(?<Minor>\d{1,3})';

select VersionNumber
from YourTable
order by
Cast(RegexMatchNamedGroup( VersionNumber, @regex, 'Major') as int),
Cast(RegexMatchNamedGroup( VersionNumber, @regex, 'Minor') as int)