34,576
社区成员
发帖
与我相关
我的任务
分享
declare @a table(a varchar(20))
insert @a select '12DR-TH'
union all select '23*16M '
union all select '2QXT'
union all select '100M'
select a from @a
order by right('0000000'+left(a+'a',patindex('%[^0-9]%',a+'a')-1),7)
/*
a
--------------------
2QXT
12DR-TH
23*16M
100M
(所影响的行数为 4 行)
*/
create table tb(col varchar(20))
insert into tb values('12DR-TH')
insert into tb values('23*16M ')
insert into tb values('2QXT')
insert into tb values('100M')
go
select col from
(
select col , val = left(col , patindex('%[^0-9]%',col) - 1) from tb where patindex('%[^0-9]%',col) > 0
union all
select col , val = col from tb where patindex('%[^0-9]%',col) <= 0
) t
order by cast(val as int)
drop table tb
/*
col
--------------------
2QXT
12DR-TH
23*16M
100M
(4 行受影响)
*/
create table tb(col varchar(20))
insert into tb values('12DR-TH')
insert into tb values('23*16M ')
insert into tb values('2QXT')
insert into tb values('100M')
go
select * from tb where patindex('%[^0-9]%',col) > 0 order by cast(left(col , patindex('%[^0-9]%',col) - 1) as int)
drop table tb
/*
col
--------------------
2QXT
12DR-TH
23*16M
100M
(4 行受影响)
*/
declare @tb table (a varchar(10))
insert into @tb select '12DR-TH'
insert into @tb select '23*16M'
insert into @tb select '2QXT'
insert into @tb select '100M'
select * from @tb order by substring(a,1,1)
select * from @tb order by left(a,1)
12DR-TH
100M
23*16M
2QXT
select * from table_1
order by char2hexint(col_1)
;