DECLARE @Result TABLE (Serial nvarchar(10),Title varchar(20))INSERT INTO @ResultSELECT '1.1','a' UNION ALLSELECT '1.2.1','b' UNION ALLSELECT '1.2.2','C' UNION ALLSELECT '1.2.3.4','G' UNION ALLSELECT '1.11','B' UNION ALLSELECT '1.2.33.4.5','Extra row' UNION ALLSELECT '2.3','B' UNION ALLSELECT '2.11','B' UNION ALLSELECT '2.2','C' UNION ALLSELECT '1.5','E' UNION ALLSELECT '1.3','E' SELECT r.* FROM @Result rCROSS APPLY (SELECT n = CHARINDEX('.',Serial,0)) p1CROSS APPLY (SELECT n = NULLIF(CHARINDEX('.',Serial,p1.n+1),0)) p2CROSS APPLY (SELECT n = NULLIF(CHARINDEX('.',Serial,p2.n+1),0)) p3CROSS APPLY (SELECT n = NULLIF(CHARINDEX('.',Serial,p3.n+1),0)) p4CROSS 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) ) xORDER 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)

-- Parse up to 4 '.' delimited elements and sort by numeric value of elementselect a1.x, a1.y, Part1 = convert(int,nullif(parsename(a1.y,4),'')), Part2 = convert(int,nullif(parsename(a1.y,3),'')), Part3 = convert(int,nullif(parsename(a1.y,2),'')), Part4 = convert(int,nullif(parsename(a1.y,1),''))from ( select a.x, y = case datalength(a.x)-datalength(replace(a.x,'.','')) when 0 then a.x+'. . . ' when 1 then a.x+'. . ' when 2 then a.x+'. ' when 3 then a.x else null end from ( select x = '10.12.14.44' union all select x = '10.12.4.44' union all select x = '1.20.3' union all select x = '1.20.41' union all select x = '1.12' union all select x = '1.2' union all select x = '1..3' union all select x = '1..3.5' union all select x = '5' union all select x = '1' ) a ) a1order by Part1, Part2, Part3, Part4

x y Part1 Part2 Part3 Part4----------- ----------------- ----------- ----------- ----------- -----------1 1. . . 1 NULL NULL NULL1..3 1..3. 1 NULL 3 NULL1..3.5 1..3.5 1 NULL 3 51.2 1.2. . 1 2 NULL NULL1.12 1.12. . 1 12 NULL NULL1.20.3 1.20.3. 1 20 3 NULL1.20.41 1.20.41. 1 20 41 NULL5 5. . . 5 NULL NULL NULL10.12.4.44 10.12.4.44 10 12 4 4410.12.14.44 10.12.14.44 10 12 14 44