ChrisM@Work (1/3/2014)

---

DECLARE @Result TABLE (Serial nvarchar(10),Title varchar(20))

INSERT INTO @Result

SELECT '1.1','a' UNION ALL

SELECT '1.2.1','b' UNION ALL

SELECT '1.2.2','C' UNION ALL

SELECT '1.2.3.4','G' UNION ALL

SELECT '1.11','B' UNION ALL

SELECT '1.2.33.4.5','Extra row' UNION ALL

SELECT '2.3','B' UNION ALL

SELECT '2.11','B' UNION ALL

SELECT '2.2','C' UNION ALL

SELECT '1.5','E' UNION ALL

SELECT '1.1','E'

SELECT r.*

FROM @Result r

CROSS APPLY (SELECT n = CHARINDEX('.',Serial,0)) p1

CROSS APPLY (SELECT n = NULLIF(CHARINDEX('.',Serial,p1.n+1),0)) p2

CROSS APPLY (SELECT n = NULLIF(CHARINDEX('.',Serial,p2.n+1),0)) p3

CROSS APPLY (SELECT n = NULLIF(CHARINDEX('.',Serial,p3.n+1),0)) p4

CROSS APPLY (

SELECT

Elem1 = LEFT(Serial, p1.n-1),

Elem2 = SUBSTRING(Serial, p1.n+1, ISNULL(p2.n-(p1.n+1),8000)),

Elem3 = SUBSTRING(Serial, p2.n+1, ISNULL(p3.n-(p2.n+1),8000)),

Elem4 = SUBSTRING(Serial, p3.n+1, ISNULL(p4.n-(p3.n+1),8000)),

Elem5 = SUBSTRING(Serial, p4.n+1, 8000)

) x

ORDER BY CAST(x.Elem1 AS INT), CAST(x.Elem2 AS INT), CAST(x.Elem3 AS INT), CAST(x.Elem4 AS INT), CAST(x.Elem5 AS INT)