By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t (string) as
select column_value from table(sys.odcivarchar2list(
'1',
'2',
'A3',
'A3.1',
'A3.1.1',
'B3',
'B3.1',
'4.1',
'4.1.1',
'4.1.2',
'3',
'3.5',
'2.1',
'2.2',
'2.7',
'2.8',
'2.7.1',
'2.7.2',
'2.17.1',
'2.17.2',
'2.18.1'
))
21 rows affected
-- simplified, with to_number, with first two sort expressions shown
select string,
TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 1)) sort1,
TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 2)) sort2
from t
ORDER BY
TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 1)) nulls first,
REGEXP_SUBSTR(string, '^\w+', 1, 1) nulls first,
TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 2)) nulls first,
TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 3)) nulls first,
TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 4)) nulls first,
TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 5)) nulls first,
string
| STRING | SORT1 | SORT2 |
|---|---|---|
| 1 | 1 | null |
| 2 | 2 | null |
| 2.1 | 2 | 1 |
| 2.2 | 2 | 2 |
| 2.7 | 2 | 7 |
| 2.7.1 | 2 | 7 |
| 2.7.2 | 2 | 7 |
| 2.8 | 2 | 8 |
| 2.17.1 | 2 | 17 |
| 2.17.2 | 2 | 17 |
| 2.18.1 | 2 | 18 |
| 3 | 3 | null |
| 3.5 | 3 | 5 |
| A3 | 3 | null |
| A3.1 | 3 | 1 |
| A3.1.1 | 3 | 1 |
| B3 | 3 | null |
| B3.1 | 3 | 1 |
| 4.1 | 4 | 1 |
| 4.1.1 | 4 | 1 |
| 4.1.2 | 4 | 1 |