Advertisements
SQL> SELECT col,
2 RegExp_Replace
3 (col || '.', '(d+.)', '000000001') p1,
4 RegExp_Replace(
5 RegExp_Replace
6 (col || '.', '(d+.)', '000000001'),
7 '0+(........).', '1') p2
8 FROM t
9 ORDER BY
10 RegExp_Replace(
11 RegExp_Replace
12 (col || '.', '(d+.)', '00000001'),
13 '0+(........).', '1');
COL P1 P2
------- ------------------------------ -----------------------
1 000000001. 00000001
1.1 000000001.000000001. 0000000100000001
1.1.1 000000001.000000001.000000001. 000000010000000100000001
1.1.2 000000001.000000001.000000002. 000000010000000100000002
1.2 000000001.000000002. 0000000100000002
1.2.4 000000001.000000002.000000004. 000000010000000200000004
1.2.5 000000001.000000002.000000005. 000000010000000200000005
1.2.10 000000001.000000002.0000000010. 000000010000000200000010
1.10.1 000000001.0000000010.000000001. 000000010000001000000001
1.10.2 000000001.0000000010.000000002. 000000010000001000000002
2 000000002. 00000002
2.1 000000002.000000001. 0000000200000001
22.333.1 0000000022.00000000333.000000001. 000000220000033300000001
13 rows selected.
As you can see, that solution works in a similar manner: converting the elements into fixed-width fields and then trimming them all to the same length, resulting in a string that is again sortable.
