Sorting records from Oracle with multiple decimal points (.)

decimal-point, oracle, oracle10g, sorting, sql

Solution

When the maximum depth is known, you can split the section in sub-sections:

SQL> SELECT SECTION FROM DATA
  2   ORDER BY to_number(regexp_substr(SECTION, '[^.]+', 1, 1)) NULLS FIRST,
  3            to_number(regexp_substr(SECTION, '[^.]+', 1, 2)) NULLS FIRST,
  4            to_number(regexp_substr(SECTION, '[^.]+', 1, 3)) NULLS FIRST;

SECTION
-------
1
1.1
1.1.2
1.1.6
6.1
6.2
[...]
8.5
10
11

If the maximum depth of sub-sections is unknown (but presumably less than a couple hundred on 8-bit character databases or less than a few thousands in ANSI-character databases), you could define a function that converts your unsortable digits into sortable characters:

SQL> CREATE OR REPLACE FUNCTION order_section (p_section VARCHAR2)
  2     RETURN VARCHAR2 IS
  3     l_result VARCHAR2(4000);
  4  BEGIN
  5     FOR i IN 1..regexp_count(p_section, '[^.]+') LOOP
  6        l_result := l_result
  7                    || CASE WHEN i > 1 THEN '.' END
  8                    || CHR(64+regexp_substr(p_section, '[^.]+', 1, i));
  9     END LOOP;
 10     RETURN l_result;
 11  END;
 12  /

Function created

SQL> SELECT SECTION, order_section(SECTION)
  2    FROM DATA
  3   ORDER BY 2;

SECTION ORDER_SECTION(SECTION)
------- -------------------------
1       A
1.1     A.A
1.1.2   A.A.B
1.1.6   A.A.F
6.1     F.A
6.2     F.B
[...]
8.5     H.E
10      J
11      K

Problem

UPDATE: ORACLE VERSION 10G I have a list of records in `Oracle` as follows, these are actually sections of various books The records are generated in the below format [main topic].[sub topic].[first level section] ..... .[last level section] ``` Sections -------- 1 7.1 6.2 7.1 7.4 6.8.3 6.8.2 10 1.1 7.6 6.1 11 8.3 8.5 1.1.2 6.4 6.6 8.4 1.1.6 6.8.1 7.7.1 7.5 7.3 ``` I want to order this like as follows ``` 1 1.1 1.1.2 1.1.6 6.2 6.4 6.5 6.6 6.7 6.8.1 6.8.2 6.8.3 7.2 7.3 7.4 7.5 7.6 7.7.1 7.7.2 8.3 8.4 8.5 10 ``` But as the field is not a `numeric` `datatype` the sorting results in something like this ``` 1 10 1.1 1.1.2 1.1.6 .... ..... 8.5 ``` How can I sort them. I am unable to convert them to number due to multiple number of decimal points. Is there any function in `oracle` that supports such a sorting technique

Original source