按照数字顺序排序,然后按照字母顺序排序:select * from 表
order by ( case when isnumeric(字段)=1 then convert(int,字段)
else (select max(convert(int,字段)) from 表 where isnumeric(字段)=1)
+ascii(字段)
end
)
order by ( case when isnumeric(字段)=1 then convert(int,字段)
else (select max(convert(int,字段)) from 表 where isnumeric(字段)=1)
+ascii(字段)
end
)
select * from tb
order by stuff(字段,1,1,'')--第二个1替换为你实际字母数
returns int
as
begin
while patindex('%[A-D]%',@str)>0
begin
set @str=stuff(@str,patindex('%[A-D]%',@str),1,'')
end
if @str=''
return 100000 return convert(int,@str)
end
go
--创建测试环境
declare @tb table
(
col varchar(20)
)
insert @tb
select 'A02354' union
select '12d54' union
select '1234' union
select 'AD' --查询
select * from @tb order by dbo.f_number(col)--结果
/*
col
--------------------
1234
12d54
A02354
AD(4 row(s) affected)
*/