Oracle 章节编号排序

测试代码

CREATE TABLE TEST (CH_CODE VARCHAR2(10));

INSERT INTO TEST (CH_CODE) VALUES ('1.1');
INSERT INTO TEST (CH_CODE) VALUES ('1.1.1');
INSERT INTO TEST (CH_CODE) VALUES ('1.1.2');
INSERT INTO TEST (CH_CODE) VALUES ('1.1.10');
INSERT INTO TEST (CH_CODE) VALUES ('2.1');
INSERT INTO TEST (CH_CODE) VALUES ('2.1.1');
INSERT INTO TEST (CH_CODE) VALUES ('2.1.2');
INSERT INTO TEST (CH_CODE) VALUES ('2.1.10');

排序查询

select * from TEST order by CH_CODE ;

1.1.2 排在1.1.10后面了，这不是我们想要的。

想要的效果

select CH_CODE,
       regexp_replace(replace(CH_CODE, '.', '.0000'), '0+([^\.]{4})', '\1')
  from TEST
 order by regexp_replace(replace(CH_CODE, '.', '.0000'),
                         '0+([^\.]{4})',
                         '\1')