select substring(学号,2,len(学号)) from tb order by 学号
SELECT * FROM TB ORDER BY REVERSE(学号)
CREATE TABLE TBTEST( NUM VARCHAR(20)) INSERT TBTEST SELECT 'nj001' UNION ALL SELECT 'wr3' UNION ALL SELECT 'pj8' UNION ALL SELECT 'wr9' UNION ALL SELECT 'we29' SELECT * FROM TBTEST ORDER BY LEFT(REVERSE(NUM),1) NUM -------------------- nj001 wr3 pj8 wr9 we29(所影响的行数为 5 行)
最后面几位一定都是数字吗?如果是,可以这样 declare @tb table(a varchar(50)) insert into @tb select 'nj001' union all select 'wr3' union all select 'pj8' union all select 'wr9' union all select 'we29' union all select 'wew239'select substring(a,patindex( '%[0-9]%',a),20) from @tb order by substring(a,patindex( '%[0-9]%',a),20)
如果你需要按数字的大小排序的话,那么可以把字段cast成int
CREATE function fc_Num (@学号 varchar(50)) returns decimal as beginwhile patindex('%[^0-9]%',@学号)>0 begin set @学号=stuff(@学号,patindex('%[^0-9]%',@学号),1,'') end if @学号 is null or isnumeric(@学号)=0 or len(ltrim(RTRIM(@学号)))=0 set @学号=0 return cast( @学号 as decimal) end goselect dbo.fc_Num(学号) as digi from A order by digi
先创建一个自定义函数: CREATE function fc_Num (@学号 varchar(50)) returns decimal as beginwhile patindex('%[^0-9]%',@学号)>0 begin set @学号=stuff(@学号,patindex('%[^0-9]%',@学号),1,'') end if @学号 is null or isnumeric(@学号)=0 or len(ltrim(RTRIM(@学号)))=0 set @学号=0 return cast( @学号 as decimal) end go -----------------------------declare @tb table(a varchar(50)) insert into @tb select 'nj001' union all select 'wr3' union all select 'pj8' union all select 'wr9' union all select 'we29' union all select 'wew239'select dbo.fc_Num(a) as '学号' from @tb order by '学号'结果:1 3 8 9 29 239
看错题了,原来最后结果也要字母的 改变下,如下: ----------------declare @tb table(a varchar(50)) insert into @tb select 'nj001' union all select 'wr3' union all select 'pj8' union all select 'wr9' union all select 'we29' union all select 'wew239'select a from @tb order by cast(substring(a,patindex( '%[0-9]%',a),20) as decimal) 结果:nj001 wr3 pj8 wr9 we29 wew239
create table T (num varchar(30))insert into T select 'nju001' insert into T select 'pj8' insert into T select 'k29' insert into T select 'wr3' insert into T select 'wryy988' Goselect num from T order by convert(int, stuff(num,1,patindex('%[0-9]%',num)-1,''))/* num ------------ nju001 wr3 pj8 k29 wryy988*/ drop table T GO
INSERT TBTEST
SELECT 'nj001' UNION ALL
SELECT 'wr3' UNION ALL
SELECT 'pj8' UNION ALL
SELECT 'wr9' UNION ALL
SELECT 'we29' SELECT * FROM TBTEST ORDER BY LEFT(REVERSE(NUM),1)
NUM
--------------------
nj001
wr3
pj8
wr9
we29(所影响的行数为 5 行)
declare @tb table(a varchar(50))
insert into @tb
select 'nj001' union all
select 'wr3' union all
select 'pj8' union all
select 'wr9' union all
select 'we29' union all
select 'wew239'select substring(a,patindex( '%[0-9]%',a),20) from @tb
order by substring(a,patindex( '%[0-9]%',a),20)
CREATE function fc_Num
(@学号 varchar(50))
returns decimal
as
beginwhile patindex('%[^0-9]%',@学号)>0
begin
set @学号=stuff(@学号,patindex('%[^0-9]%',@学号),1,'')
end
if @学号 is null or isnumeric(@学号)=0 or len(ltrim(RTRIM(@学号)))=0
set @学号=0
return cast( @学号 as decimal)
end
goselect dbo.fc_Num(学号) as digi from A order by digi
先创建一个自定义函数:
CREATE function fc_Num
(@学号 varchar(50))
returns decimal
as
beginwhile patindex('%[^0-9]%',@学号)>0
begin
set @学号=stuff(@学号,patindex('%[^0-9]%',@学号),1,'')
end
if @学号 is null or isnumeric(@学号)=0 or len(ltrim(RTRIM(@学号)))=0
set @学号=0
return cast( @学号 as decimal)
end
go
-----------------------------declare @tb table(a varchar(50))
insert into @tb
select 'nj001' union all
select 'wr3' union all
select 'pj8' union all
select 'wr9' union all
select 'we29' union all
select 'wew239'select dbo.fc_Num(a) as '学号' from @tb order by '学号'结果:1
3
8
9
29
239
看错题了,原来最后结果也要字母的
改变下,如下:
----------------declare @tb table(a varchar(50))
insert into @tb
select 'nj001' union all
select 'wr3' union all
select 'pj8' union all
select 'wr9' union all
select 'we29' union all
select 'wew239'select a from @tb
order by cast(substring(a,patindex( '%[0-9]%',a),20) as decimal)
结果:nj001
wr3
pj8
wr9
we29
wew239
create table T (num varchar(30))insert into T select 'nju001'
insert into T select 'pj8'
insert into T select 'k29'
insert into T select 'wr3'
insert into T select 'wryy988'
Goselect num from T
order by convert(int, stuff(num,1,patindex('%[0-9]%',num)-1,''))/*
num
------------
nju001
wr3
pj8
k29
wryy988*/
drop table T
GO