Order By Sequence SQL - Need Help

[wheels0323]

select distinct homelaborleveldsc2 from vp_person
and employmentstatus = 'Active'
order by homelaborleveldsc2

Results:
District 1
District 10
District 11
District 12
District 2
District 3
District 4
District 5
District 6
District 7
District 8
District 9
Drivers
Home Office
Regional Office
Support
Warehouse

select distinct homelaborlevelname2
from vp_person
where homelaborlevelname2 <> '-'
and homelaborlevelname2 <> 'ADM'
and homelaborlevelname2 <> 'BUY'
and homelaborlevelname2 <> 'ITA'
and homelaborlevelname2 <> 'OS'
and homelaborlevelname2 <> '0'
and employmentstatus = 'Active'
order by homelaborlevelname2

Results:
D1
D10
D11
D12
D2
D3
D4
D5
D6
D7
D8
D9
HQ
KEE
RO
SUP
WHS


This is my problem. I need to return my results by district 1st in order of D1-D12, then the others. Naturally the 'D's come back first in sequential data, yet when I try to do any sort on them so that it comes back D1, D2, D3, D4, D5, D6, D7, D8, D9, D10, D11, D12 then the others I can't get it to work. I included two different fields above that in essence brings back the same data. Just need one or the other to work.

Ideally what Iam looking for is the order to be..(if looking at first one)

Results:
District 1
District 2
District 3
District 4
District 5
District 6
District 7
District 8
District 9
District 10
District 11
District 12
Drivers
Home Office
Regional Office
Support
Warehouse


Thanks.. Any help would be appreciated.

 

[markros]

It is slightly complex - you need to Sort by

Column with removed number and then by the number.

You may want to check this blog

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/extracting-numbers-with-sql-server

 

[RyanEK]

With thanks to Markos the blogs link guru... you could do the following?

declare @temp table (code varchar(10))
insert into @temp values ('D11')
insert into @temp values ('D12')
insert into @temp values ('D1')
insert into @temp values ('D2')
insert into @temp values ('D3')
insert into @temp values ('Home')
insert into @temp values ('Drivers')

select code
from 
(select code, LEFT(SUBSTRING(code, PATINDEX('%[0-9.-]%', code), 8000), 
 PATINDEX('%[^0-9.-]%', SUBSTRING(code, PATINDEX('%[0-9.-]%', code), 8000) + 'X')-1) as ivalue
 from @temp 
) as t
order by replace(code, ivalue,''), cast(ivalue as int)

Ryan