17,086
社区成员
发帖
与我相关
我的任务
分享
create table tb_stdital
(sitem varchar(10));
insert into tb_stdital values('C.1.1');
insert into tb_stdital values('C.1.2');
insert into tb_stdital values('C.1.3');
insert into tb_stdital values('C.1.2');
insert into tb_stdital values('C.1.2.1');
insert into tb_stdital values('C.1.2.2');
insert into tb_stdital values('C.1.2.1');
insert into tb_stdital values('C.1.3');
insert into tb_stdital values('C.1.3.1');
SELECT * FROM tb_stdital ORDER BY sitem ASC;
--查询结果
SITEM
C.1.1
C.1.2
C.1.2
C.1.2.1
C.1.2.1
C.1.2.2
C.1.3
C.1.3
C.1.3.1
with tmp as
(
select '1.1' chp from dual union all
select '1.1.2' chp from dual union all
select '1.1.3' chp from dual union all
select '1.2' chp from dual union all
select '1.2.1' chp from dual union all
select '1.3' chp from dual union all
select 'C.1.2.2' chp from dual
)
select chp,regexp_substr(chp, '([0-9]+\.?)+')
from tmp
order by regexp_substr(chp, '([0-9]+\.?)+');
CHP REGEXP_SUBSTR(CHP,'([0-9]+\.?)+')
---------- ---------------------------------
1.1 1.1
1.1.2 1.1.2
1.1.3 1.1.3
1.2 1.2
1.2.1 1.2.1
C.1.2.2 1.2.2
1.3 1.3