You may want to look at how you store the numbers since having them with text makes it difficult to sort but this is a start:

declare @TTable Table(

House_no varchar(100)

)

insert into @TTable(House_no)

values ('3-4-53 . HYDERABAD'),

('3-4-199/1.ASVV'),

('3-4-vizag'),

('3-4-22,nagar'),

('3-4-45.old colony'),

('3-4-66/99'),

('3-4-'),

('2/4'),

('2-4'),

('3-5-hasd'),

('3-6van'),

('4-5-123asvr')

select * from @TTable

order by left(House_no, 1), left(House_no, 3)

Mark