Hi all,
I was wondering if anyone could advise on how we can implement a sorting like those we have in table of contents.
Imagine, we have a sample data like this;
WITH data AS (
SELECT '1' AS n FROM dual UNION ALL
SELECT '1.1' FROM dual UNION ALL
SELECT '1.1.1' FROM dual UNION ALL
SELECT '2' FROM dual UNION ALL
SELECT '2.1' FROM dual UNION ALL
SELECT '2.2' FROM dual UNION ALL
SELECT '2.2.1' FROM dual UNION ALL
SELECT '3' FROM dual UNION ALL
SELECT '10' FROM dual UNION ALL
SELECT '10.1' FROM dual UNION ALL
SELECT '10.2' FROM dual UNION ALL
SELECT '10.3' FROM dual UNION ALL
SELECT '10.4' FROM dual UNION ALL
SELECT '11' FROM dual UNION ALL
SELECT '11.1' FROM dual UNION ALL
SELECT '11.1.1' FROM dual UNION ALL
SELECT '11.1.1.1' FROM dual UNION ALL
SELECT '12' FROM dual UNION ALL
SELECT '12.1' FROM dual UNION ALL
SELECT '20' FROM dual UNION ALL
SELECT '20.1' FROM dual UNION ALL
SELECT '20.1.1.a' FROM dual
)
SELECT n, REGEXP_REPLACE(n, '(\d+)', LPAD('\1', 4, '0'))
FROM data
ORDER BY n;
Is there a way to display our data “naturally”? 1, 1.1, 1.1.1, 2, 3… 10, 10.1, etc. Like a table of contents basically. The problem I face is that we have a variable number of “levels”.
I tried using a regular expression. I was thinking to split it and LPAD it to have leading zeroes. Problem is that when back reference is more than 1 char, it get messy again. I was wondering if we could have a function “inside the back reference”.
SELECT n, REGEXP_REPLACE(n, '(\d+)', 'LPAD(\1, 4, 0)')
Also, another issue I have is that sometimes we have a mix between letters and numbers. But the behavior should be the same. It should be sorted alphabetically when it's letters; and when it's number, it should be sorted somehow “naturally”; like a Word table of contents.
Any suggestions?