17,140
社区成员




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