Here are 2 ways that are essentially identical:

WITH SampleData (houseno) AS

(

SELECT '3-13-1'

UNION ALL SELECT '3-13-3/a'

UNION ALL SELECT '3-13-3/b'

UNION ALL SELECT '3-13-2/a'

UNION ALL SELECT '3-13-4'

UNION ALL SELECT '3-13-6'

UNION ALL SELECT '3-13-5/a'

UNION ALL SELECT '3-13-4/c'

)

SELECT houseno

FROM SampleData a

CROSS APPLY

(

SELECT FirstPart = 0+LEFT(houseno, CHARINDEX('-', houseno) - 1)

,SecondPart = SUBSTRING(houseno, CHARINDEX('-', houseno) + 1, 99)

) b

CROSS APPLY

(

SELECT SecondPart = 0+LEFT(SecondPart, CHARINDEX('-', SecondPart) - 1)

,ThirdPart = SUBSTRING(SecondPart, CHARINDEX('-', SecondPart) + 1, 99)

) c

CROSS APPLY

(

SELECT ThirdPart = 0+LEFT(ThirdPart, CHARINDEX('/', ThirdPart + '/') - 1)

,FourthPart = CASE CHARINDEX('/', ThirdPart) WHEN 0 THEN ''

ELSE SUBSTRING(ThirdPart, CHARINDEX('/', ThirdPart) + 1, 99) END

) d

ORDER BY b.FirstPart, c.SecondPart, d.ThirdPart, d.FourthPart;

WITH SampleData (houseno) AS

(

SELECT '3-13-1'

UNION ALL SELECT '3-13-3/a'

UNION ALL SELECT '3-13-3/b'

UNION ALL SELECT '3-13-2/a'

UNION ALL SELECT '3-13-4'

UNION ALL SELECT '3-13-6'

UNION ALL SELECT '3-13-5/a'

UNION ALL SELECT '3-13-4/c'

)

SELECT houseno

FROM SampleData a

CROSS APPLY DelimitedSplit8K(houseno, '-') b

CROSS APPLY

(

SELECT item1=CASE WHEN CHARINDEX('/', item) = 0 THEN Item ELSE LEFT(item, CHARINDEX('/', item) - 1) END

,item2=CASE WHEN CHARINDEX('/', item) = 0 THEN '' ELSE SUBSTRING(item, CHARINDEX('/', item) + 1, 99) END

) c

GROUP BY houseno

ORDER BY

MAX(CASE WHEN ItemNumber = 1 THEN CAST(Item AS INT) END)

,MAX(CASE WHEN ItemNumber = 2 THEN CAST(Item AS INT) END)

,MAX(CASE WHEN ItemNumber = 3 THEN CAST(Item1 AS INT) END)

,MAX(CASE WHEN ItemNumber = 3 THEN Item2 END)

The DelimitedSplit8K FUNCTION can be found here[/url], however take care if you use this approach because DelimitedSplit8K is designed to be used with VARCHAR(8000) data types. So any NVARCHAR(MAX) address that is greater than 4000 bytes may get truncated, although I fail to see why you need 2GB to store a house number.

The first method uses cascading CROSS APPLYs to split out he pieces of the house number one at a time, casting each (except the last) so as to sort the house numbers as if they are integers.