sql排序问题,字符串中有全数字和含中文的, 要求全数字的转换成整数排序,含中文的默认排在前面或者最后
排序的字段属性nvarchar(50),有全数字,全中文,数字中文混合的,还有是小数的,我昏倒,全数字占大部分,
例如:
num tmp
1 20
2 2
3 19
4 边脚
5 12a
6 四
7 2-1所以想把整数的排序,其他不规则的放在后面就可以
排序的字段属性nvarchar(50),有全数字,全中文,数字中文混合的,还有是小数的,我昏倒,全数字占大部分,
例如:
num tmp
1 20
2 2
3 19
4 边脚
5 12a
6 四
7 2-1所以想把整数的排序,其他不规则的放在后面就可以
from tb
order by case when isnumber(tmp)=1 then temp else 0 end
case when isnumeric(tmp)=1 then 0 else 1 end
, case when isnumeric(tmp)=1 then cast(tmp as numeric(28,8)) end
,tmp
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (num int,tmp varchar(4))
insert into #tb
select 1,'20' union all
select 2,'2' union all
select 3,'19' union all
select 4,'边脚' union all
select 5,'12a' union all
select 6,'四' union all
select 7,'2-1'select * from #tb order by case when isnumeric(tmp)=1 then 0 else 1 end,case when isnumeric(tmp)=1 then cast(tmp as int) else 9999999 end num tmp
----------- ----
2 2
3 19
1 20
4 边脚
5 12a
6 四
7 2-1(7 行受影响)
order by
case when isnumeric(tmp)=1 then 0 else 1 end
, case when isnumeric(tmp)=1 then cast(tmp as numeric(28,8)) end
,tmp