create table tb(col varchar(10)) insert into tb values('SJY234') insert into tb values('SN3' ) insert into tb values('SJY4' ) insert into tb values('SN223' ) insert into tb values('SP23' ) insert into tb values('SB3' ) insert into tb values('SB23' ) go select * from tb order by substring(col, 1 ,patindex('%[^a-z]%',col) - 1) , cast(substring(col, patindex('%[^a-z]%',col) + 1 , len(col)) as int)drop table tb/* col ---------- SB3 SB23 SJY4 SJY234 SN3 SN223 SP23(所影响的行数为 7 行) */
select * from [Table] order by a+replicate('0',100-len(a))
create table tb(col varchar(10)) insert into tb values('SJY234') insert into tb values('SN3' ) insert into tb values('SJY4' ) insert into tb values('SN223' ) insert into tb values('SP23' ) insert into tb values('SB3' ) insert into tb values('SB23' ) go select * from tb where col is not null order by substring(col, 1 ,patindex('%[^a-z]%',col) - 1) , cast(substring(col, patindex('%[^a-z]%',col) + 1 , len(col)) as int)drop table tb
create table tb(col varchar(10)) insert into tb values('SJY234') insert into tb values('SN3' ) insert into tb values('SJY4' ) insert into tb values('SN223' ) insert into tb values('SP23' ) insert into tb values('SB3' ) insert into tb values('SB23' ) insert into tb values(null) goselect * from tb order by case when col is null then 1 else 2 end , substring(col, 1 ,patindex('%[^a-z]%',col) - 1) , cast(substring(col, patindex('%[^a-z]%',col) + 1 , len(col)) as int)drop table tb
create table tb(col varchar(10)) insert into tb values('SJY234') insert into tb values('SN3' ) insert into tb values('SJY4' ) insert into tb values('SN223' ) insert into tb values('SP23' ) insert into tb values('SB3' ) insert into tb values('SB23' ) insert into tb values(null) goselect * from tb order by case when col is null then 1 else 2 end , substring(col, 1 ,patindex('%[^a-z]%',col) - 1) , cast(substring(col, patindex('%[^a-z]%',col) + 1 , len(col)) as int)drop table tb/* col ---------- NULL SB3 SB23 SJY4 SJY234 SN3 SN223 SP23 */
insert into tb values('SJY234')
insert into tb values('SN3' )
insert into tb values('SJY4' )
insert into tb values('SN223' )
insert into tb values('SP23' )
insert into tb values('SB3' )
insert into tb values('SB23' )
go
select * from tb order by substring(col, 1 ,patindex('%[^a-z]%',col) - 1) , cast(substring(col, patindex('%[^a-z]%',col) + 1 , len(col)) as int)drop table tb/*
col
----------
SB3
SB23
SJY4
SJY234
SN3
SN223
SP23(所影响的行数为 7 行)
*/
insert into tb values('SJY234')
insert into tb values('SN3' )
insert into tb values('SJY4' )
insert into tb values('SN223' )
insert into tb values('SP23' )
insert into tb values('SB3' )
insert into tb values('SB23' )
go
select * from tb where col is not null order by substring(col, 1 ,patindex('%[^a-z]%',col) - 1) , cast(substring(col, patindex('%[^a-z]%',col) + 1 , len(col)) as int)drop table tb
create table tb(col varchar(10))
insert into tb values('SJY234')
insert into tb values('SN3' )
insert into tb values('SJY4' )
insert into tb values('SN223' )
insert into tb values('SP23' )
insert into tb values('SB3' )
insert into tb values('SB23' )
insert into tb values(null) goselect * from tb order by case when col is null then 1 else 2 end , substring(col, 1 ,patindex('%[^a-z]%',col) - 1) , cast(substring(col, patindex('%[^a-z]%',col) + 1 , len(col)) as int)drop table tb
insert into tb values('SJY234')
insert into tb values('SN3' )
insert into tb values('SJY4' )
insert into tb values('SN223' )
insert into tb values('SP23' )
insert into tb values('SB3' )
insert into tb values('SB23' )
insert into tb values(null) goselect * from tb order by case when col is null then 1 else 2 end , substring(col, 1 ,patindex('%[^a-z]%',col) - 1) , cast(substring(col, patindex('%[^a-z]%',col) + 1 , len(col)) as int)drop table tb/*
col
----------
NULL
SB3
SB23
SJY4
SJY234
SN3
SN223
SP23
*/