Solution without regexp and functions (suppose t is a table with source data):
select * from t
order by
(
select
sum(
to_number(substr(
sections,
decode(level,
1,1,
instr(sections, '.', 1, level-1)+1
),
decode(instr(sections, '.', 1, level),
0, length(sections),
instr(sections, '.', 1, level)
-
decode(level,
1,1,
instr(sections, '.', 1, level-1)+1
)
)
))
* power(1000, 10-level)
)
from dual
connect by instr(sections,'.',1,level-1) > 0
)
SQLFiddle example
Main idea is to calculate number, wich indicates priority of each row. Suppose, we have 33.17.21.2 value. This string may be treated as a number in hypotetical numeral system with base Q like a hexadecimal numbers represents IPv4 address, and then converted to a numeric representation:
33*(Q^3) + 17*(Q^2) + 21*(Q^1) + 2*(Q^0)
For example, if Q=100 then number from exmple is
33*1000000 + 17*10000 + 21*100 + 2*1 = 33172102
First trouble with this approach is that each level numbers required to be less than choosed Q value. It's by design and can't be eleminated.
Next is that we don't know how many levels at all, we have 7.1 and 2.2.2.2.2.2, and shorter one most come first. Therefore while calculating value it starts from some fixed power N and then degrades power of Q, so in case of Q=100 and N=3 sequence of multipilers starts with this numbers:
1000000, 10000, 100, 1, 1/100, 1/10000, 1/1000000, ...
In code above Q=1000 and N=10, but this may be changed depending on required parameters.
Number of levels limited by choosed Q value and precision of Oracle number type. Theoretically it's possible to build expression for longer strings by splitting string into parts.
Rest of the code is just hierarchical query for splitting string to sequence of numbers.
Update
Same approach may be used easily just for strings: '20' comes before '8' because information about second digit are missing. If we pad both values to some fixed length it ordered as expected: '008' < '020', so it's possible to deal with strings only:
select * from t order by
(
select
listagg(
lpad(
substr(
sections,
decode( level,
1,1,
instr(sections, '.', 1, level-1)+1
),
decode(instr(sections, '.', 1, level),
0, length(sections),
instr(sections, '.', 1, level)
-
decode(level,
1, 1,
instr(sections, '.', 1, level-1)+1
)
)
),
8,'0'
),
'-'
) within group (order by level)
from dual
connect by instr(sections,'.',1,level-1) > 0
)
With string length limitation of 4000 chars and 9 digits on each level with single separation symbol ('-' in example above) it's possible to handle 400 levels of hierarchy.
Main disadvantage of this method is a memory consumption and comparison speed.
From other side, lack of a conversion to a number makes it compatible even with mixed chapter numbering( things like '13.3.a.vii' or 'III.A.13.2' (Ooops ... roman numerals handled improperly)
In case of decimal-number-only numbering variant with strings may be compacted by translation of numbers to hexadecimal representation. With 4 hex symbols it's possible to handle 16535 numbers on each level, and with 8 symbols - full 32-bit number which more than enough for most applications.
select * from t order by
(
select
listagg(
lpad(
trim(to_char(
to_number(substr(
sections,
decode( level,
1,1,
instr(sections, '.', 1, level-1)+1
),
decode(instr(sections, '.', 1, level),
0, length(sections),
instr(sections, '.', 1, level)
-
decode(level,
1, 1,
instr(sections, '.', 1, level-1)+1
)
)
)),
'XXXXXXXX'
)),
4,'0'
),
'-'
) within group (order by level)
from dual
connect by instr(sections,'.',1,level-1) > 0
)
P.S. Of course, it's possible to use all expressions above in select list to examine calculated values instead of using it in order by.
