34,590
社区成员
发帖
与我相关
我的任务
分享
create table test_2(id int,c1 varchar(10))
insert into test_2
select 1,'cd1'
union
select 2,'cd2'
union
select 3,'cd10'
union
select 4,'cd21'
union
select 5,'cd20'
select * from test_2 order by cast(ltrim(replace(c1,'cd',' ')) as int)
--1 cd1
--2 cd2
--3 cd10
--5 cd20
--4 cd21
--比较完整通用的:
SELECT *
FROM tb
ORDER BY LEFT(col, PATINDEX('%[0-9]%', col)-1),
STUFF(col, 1, PATINDEX('%[0-9]%', col)-1, '')*1
ORDER BY STUFF(COL,1,2,'')*1