/* ISNUMERIC()函数 确定表达式是否为一个有效的数字类型。*/ create table #t(col1 int,col2 varchar(20))insert into #t(col1,col2) select 1,'10' union all select 2,'7' union all select 3,'8' union all select 4,'6' union all select 5,'长10'select *,ISNUMERIC(col2) as 是否数值 from #tdrop table #t
--汉子是有规律的话.可以这么截取后,排序如下: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(所影响的行数为 5 行)
select col1,col2 from table1 order by isnumeric(col2) desc,case when isnumeric(col2)=1 then convert(int,col2) end
--支持小数的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(所影响的行数为 6 行)
前补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)/* col1 col2 ----------- ---------- 4 6 2 7 3 8 1 10 5 长10 */
mislrb(上班看看早报,上上CSDN,下班看看电影 非常厉害 另外感谢各位支持!谢谢!
select col1,col2 from table1 order by isnumeric(col2) desc,case when isnumeric(col2)=1 then convert(money,col2) end 就支持小数了!
视图的效果如下,
col1 col2 col3
1 10 10
2 7 7
3 8 8
4 6 6
5 长10 10那么就可以排序了,如果汉字的数目不定,那就没办法了。
ISNUMERIC()函数
确定表达式是否为一个有效的数字类型。*/
create table #t(col1 int,col2 varchar(20))insert into #t(col1,col2)
select 1,'10' union all
select 2,'7' union all
select 3,'8' union all
select 4,'6' union all
select 5,'长10'select *,ISNUMERIC(col2) as 是否数值
from #tdrop table #t
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(所影响的行数为 5 行)
from table1
order by isnumeric(col2) desc,case when isnumeric(col2)=1 then convert(int,col2) end
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(所影响的行数为 6 行)
-------------------------------------------------------------------------
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)/*
col1 col2
----------- ----------
4 6
2 7
3 8
1 10
5 长10
*/
另外感谢各位支持!谢谢!
from table1
order by isnumeric(col2) desc,case when isnumeric(col2)=1 then convert(money,col2) end
就支持小数了!