22,209
社区成员
发帖
与我相关
我的任务
分享
SELECT *
FROM
(VALUES ('A11')
, ('A23')
, ('B20')
, ('C18')
, ('AB11')
, ('BC10')
, ('ABC100')) t (Col)
ORDER BY PATINDEX('%[0-9]%',Col),Col;
/*
A11
A23
B20
C18
AB11
BC10
ABC100
*/
with t(c1) as
(
select 'A11' union all
select 'ABC100' union all
select 'C18' union all
select 'A23' union all
select 'AB11' union all
select 'B20' union all
select 'BC10'
)
select * from (
select c1, MIN(number) number from t a left join
(select number+1 number from master..spt_values where type='P') b on b.number<=LEN(a.c1)
where ISNUMERIC(substring(a.c1, number, 1))>0
group by c1
) aa
order by number, c1
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([col] nvarchar(26))
Insert #T
select N'A11' union all
select N'A23' union all
select N'B20' union all
select N'C18' union all
select N'AB11' union all
select N'BC10' union all
select N'ABC100'
Go
--测试数据结束
Select * from #T ORDER BY col