前补0对齐(借用zlp321002的测试数据):
-------------------------------------------------------------------------
declare @t table(col1 int,col2 varchar(10))
insert into @t select 1,'10'
union all select 2,'7'
union all select 3,'8'
union all select 4,'6'
union all select 5,'长10'
--查询
select col1,
col2
from @t
order by right('0000000000'+col2,10)
declare @t table(col1 int,col2 varchar(10))
insert into @t select 1,'10'
union all select 2,'7'
union all select 3,'8'
union all select 4,'6'
union all select 5,'长10'
union all select 5,'短6.3'
--查询
select col1,
col2
from @t
order by cast(stuff(col2,1,patindex('%[0-9]%',col2)-1,'') as decimal(8,4)) asc
--结果
col1 col2
----------- ----------
4 6
5 短6.3
2 7
3 8
5 长10
1 10
declare @t table(col1 int,col2 varchar(10))
insert into @t select 1,'10'
union all select 2,'7'
union all select 3,'8'
union all select 4,'6'
union all select 5,'长10'
--查询
select col1,
col2
from @t
order by cast(stuff(col2,1,patindex('%[0-9]%',col2)-1,'') as int) asc
--结果
col1 col2
----------- ----------
4 6
2 7
3 8
5 长10
1 10