;WITH MyAddresses (HouseNo) AS ( -- meagre sample data set
SELECT '3-4-53 . HYDERABAD' UNION ALL
SELECT '3-4-199/1.ASVV' UNION ALL
SELECT '3-4-vizag' UNION ALL
SELECT '3-4-22,nagar' UNION ALL
SELECT '3-4-45.old colony' UNION ALL
SELECT '3-4-66/99' UNION ALL
SELECT '3-4-' UNION ALL
SELECT '2/4' UNION ALL
SELECT '2-4' UNION ALL
SELECT '3-5-hasd' UNION ALL
SELECT '3-6van' UNION ALL
SELECT '4-5-123asvr'
)
SELECT a.*, x.*, y.*, z.*
FROM MyAddresses a
-- get the position of the hyphens
CROSS APPLY (SELECT n = NULLIF(CHARINDEX('-',a.HouseNo,1),0)) p1
CROSS APPLY (SELECT n = NULLIF(CHARINDEX('-',a.HouseNo,p1.n+1),0)) p2
CROSS APPLY ( -- split the elements from the string
SELECT
Element1 = LEFT(a.HouseNo,ISNULL(p1.n-1,8000)),
Element2 = SUBSTRING(a.HouseNo,p1.n+1,ISNULL(p2.n-p1.n-1,8000)),
Element3 = SUBSTRING(a.HouseNo,p2.n+1,8000)
) x
CROSS APPLY ( -- calculate the length of the numeric - first - part of element 3
SELECT n = NULLIF(MIN(n),1)
FROM (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)) t
WHERE SUBSTRING(Element3,n,1) NOT LIKE '[0-9]'
) y
CROSS APPLY ( -- jig around with the elements to force the desired sort order
SELECT
Element1 = CASE WHEN Element1 NOT LIKE '%/%' THEN Element1+CHAR(255) ELSE Element1 END,
Element2,
Element3 = CASE WHEN y.n IS NULL THEN Element3 ELSE CHAR(255)+RIGHT('000000'+LEFT(Element3,y.n),6) END
) z
ORDER BY z.Element1, z.Element2, z.Element3
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden