Create table #1(id varchar(50))insert #1
select '外办发[2005]1号'
union all select
'外办发[2005]2号'
union all select
'外办发[2005]6号'
union all select
'外办发[2005]12号'
union all select
'外办发[2005]33号'
union all select
'外办发[2005]50号'
union all select
'外办发[2005]123号'select * from #1
order by left(id,9),cast(left(substring(id,10,10),patindex('%[^0-9]%',substring(id,10,10))-1) as int)
select '外办发[2005]1号'
union all select
'外办发[2005]2号'
union all select
'外办发[2005]6号'
union all select
'外办发[2005]12号'
union all select
'外办发[2005]33号'
union all select
'外办发[2005]50号'
union all select
'外办发[2005]123号'select * from #1
order by left(id,9),cast(left(substring(id,10,10),patindex('%[^0-9]%',substring(id,10,10))-1) as int)
order by cast(left(right(id,len(id)-9),len(right(id,len(id)-9))-1) as int)
select '外办发[2005]1号'
union all select
'外办发[2005]2号'
union all select
'外办发[2005]6号'
union all select
'外办发[2005]12号'
union all select
'外办发[2005]33号'
union all select
'外办发[2005]50号'
union all select
'外办发[2005]123号'select * from @t
order by cast(left(right(id,len(id)-9),len(right(id,len(id)-9))-1) as int)外办发[2005]1号
外办发[2005]2号
外办发[2005]6号
外办发[2005]12号
外办发[2005]33号
外办发[2005]50号
外办发[2005]123号
select * from #1
order by left(id,charindex(']',id)),cast(left(substring(id,10,10),patindex('%[^0-9]%',substring(id,10,10))-1) as int)
select '外办发[2005]1号'
union all select
'外办发[2005]2号'
union all select
'外办发[2005]6号'
union all select
'外办发[2005]12号'
union all select
'外办发[2005]33号'
union all select
'外办发[2005]50号'
union all select
'外办发[2005]123号'select * from #1
order by left(id,charindex(']',id)),cast(left(substring(id,charindex(']',id)+1,10),patindex('%[^0-9]%',substring(id,10,10))-1) as int)