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',
'2.1',
'2.2',
'2.7',
'2.8',
'2.7.1',
'2.7.2',
'2.17.1',
'2.17.2',
'2.18.1'
))
19 rows affected
-- original, with first two sort expressions shown
select string,
REGEXP_REPLACE(TRIM(REGEXP_SUBSTR(string, '\d', 1, 1)),'(\d+\.)(\d+\.)?(\d+\.)?(\d+\.)?', '\1') sort1,
REGEXP_REPLACE(TRIM(REGEXP_SUBSTR(string, '\d', 2, 2)),'(\d+\.)(\d+\.)?(\d+\.)?(\d+\.)?', '\2') sort2
from t
ORDER BY
REGEXP_REPLACE(TRIM(REGEXP_SUBSTR(string, '\d', 1, 1)),'(\d+\.)(\d+\.)?(\d+\.)?(\d+\.)?', '\1') NULLS FIRST,
REGEXP_REPLACE(TRIM(REGEXP_SUBSTR(string, '\d', 2, 2)),'(\d+\.)(\d+\.)?(\d+\.)?(\d+\.)?', '\2') NULLS FIRST,
REGEXP_REPLACE(TRIM(REGEXP_SUBSTR(string, '\d', 3, 3)),'(\d+\.)(\d+\.)?(\d+\.)?(\d+\.)?', '\3') NULLS FIRST,
REGEXP_REPLACE(TRIM(REGEXP_SUBSTR(string, '\d', 4, 4)),'(\d+\.)(\d+\.)?(\d+\.)?(\d+\.)?', '\4') NULLS FIRST,
REGEXP_REPLACE(TRIM(REGEXP_SUBSTR(string, '\d', 5, 5)),'(\d+\.)(\d+\.)?(\d+\.)?(\d+\.)?', '\5') NULLS FIRST,
string
| STRING | SORT1 | SORT2 |
|---|---|---|
| 1 | 1 | null |
| 2 | 2 | null |
| 2.1 | 2 | null |
| 2.2 | 2 | null |
| 2.7 | 2 | null |
| 2.8 | 2 | null |
| 2.7.1 | 2 | 1 |
| 2.7.2 | 2 | 2 |
| 2.17.1 | 2 | 7 |
| 2.17.2 | 2 | 7 |
| 2.18.1 | 2 | 8 |
| A3 | 3 | null |
| B3 | 3 | null |
| A3.1 | 3 | 1 |
| A3.1.1 | 3 | 1 |
| B3.1 | 3 | 1 |
| 4.1 | 4 | null |
| 4.1.1 | 4 | 1 |
| 4.1.2 | 4 | 2 |
-- 'position' changed to 1, with first two sort expressions shown
-- still not right if elements have multiple digits; can fix with to_number
select string,
REGEXP_REPLACE(TRIM(REGEXP_SUBSTR(string, '\d', 1, 1)),'(\d+\.)(\d+\.)?(\d+\.)?(\d+\.)?', '\1') sort1,
REGEXP_REPLACE(TRIM(REGEXP_SUBSTR(string, '\d', 1, 2)),'(\d+\.)(\d+\.)?(\d+\.)?(\d+\.)?', '\2') sort2
from t
ORDER BY
REGEXP_REPLACE(TRIM(REGEXP_SUBSTR(string, '\d', 1, 1)),'(\d+\.)(\d+\.)?(\d+\.)?(\d+\.)?', '\1') NULLS FIRST,
REGEXP_REPLACE(TRIM(REGEXP_SUBSTR(string, '\d', 1, 2)),'(\d+\.)(\d+\.)?(\d+\.)?(\d+\.)?', '\2') NULLS FIRST,
REGEXP_REPLACE(TRIM(REGEXP_SUBSTR(string, '\d', 1, 3)),'(\d+\.)(\d+\.)?(\d+\.)?(\d+\.)?', '\3') NULLS FIRST,
REGEXP_REPLACE(TRIM(REGEXP_SUBSTR(string, '\d', 1, 4)),'(\d+\.)(\d+\.)?(\d+\.)?(\d+\.)?', '\4') NULLS FIRST,
REGEXP_REPLACE(TRIM(REGEXP_SUBSTR(string, '\d', 1, 5)),'(\d+\.)(\d+\.)?(\d+\.)?(\d+\.)?', '\5') NULLS FIRST,
string
| STRING | SORT1 | SORT2 |
|---|---|---|
| 1 | 1 | null |
| 2 | 2 | null |
| 2.1 | 2 | 1 |
| 2.17.1 | 2 | 1 |
| 2.17.2 | 2 | 1 |
| 2.18.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 |
| A3 | 3 | null |
| B3 | 3 | null |
| A3.1 | 3 | 1 |
| B3.1 | 3 | 1 |
| A3.1.1 | 3 | 1 |
| 4.1 | 4 | 1 |
| 4.1.1 | 4 | 1 |
| 4.1.2 | 4 | 1 |
-- simplified, with first two sort expressions shown
-- still not right if elements have multiple digits; can fix with to_number
select string,
REGEXP_SUBSTR(string, '\d+', 1, 1) sort1,
REGEXP_SUBSTR(string, '\d+', 1, 2) sort2
from t
ORDER BY
REGEXP_SUBSTR(string, '\d+', 1, 1) nulls first,
REGEXP_SUBSTR(string, '\d+', 1, 2) nulls first,
REGEXP_SUBSTR(string, '\d+', 1, 3) nulls first,
REGEXP_SUBSTR(string, '\d+', 1, 4) nulls first,
REGEXP_SUBSTR(string, '\d+', 1, 5) nulls first,
string
| STRING | SORT1 | SORT2 |
|---|---|---|
| 1 | 1 | null |
| 2 | 2 | null |
| 2.1 | 2 | 1 |
| 2.17.1 | 2 | 17 |
| 2.17.2 | 2 | 17 |
| 2.18.1 | 2 | 18 |
| 2.2 | 2 | 2 |
| 2.7 | 2 | 7 |
| 2.7.1 | 2 | 7 |
| 2.7.2 | 2 | 7 |
| 2.8 | 2 | 8 |
| A3 | 3 | null |
| B3 | 3 | null |
| A3.1 | 3 | 1 |
| B3.1 | 3 | 1 |
| A3.1.1 | 3 | 1 |
| 4.1 | 4 | 1 |
| 4.1.1 | 4 | 1 |
| 4.1.2 | 4 | 1 |
-- simplified, with to_number, with all sort expressions shown
select string,
TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 1)) sort1,
TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 2)) sort2,
TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 3)) sort3,
TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 4)) sort4,
TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 5)) sort5
from t
ORDER BY
TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 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 | SORT3 | SORT4 | SORT5 |
|---|---|---|---|---|---|
| 1 | 1 | null | null | null | null |
| 2 | 2 | null | null | null | null |
| 2.1 | 2 | 1 | null | null | null |
| 2.2 | 2 | 2 | null | null | null |
| 2.7 | 2 | 7 | null | null | null |
| 2.7.1 | 2 | 7 | 1 | null | null |
| 2.7.2 | 2 | 7 | 2 | null | null |
| 2.8 | 2 | 8 | null | null | null |
| 2.17.1 | 2 | 17 | 1 | null | null |
| 2.17.2 | 2 | 17 | 2 | null | null |
| 2.18.1 | 2 | 18 | 1 | null | null |
| A3 | 3 | null | null | null | null |
| B3 | 3 | null | null | null | null |
| A3.1 | 3 | 1 | null | null | null |
| B3.1 | 3 | 1 | null | null | null |
| A3.1.1 | 3 | 1 | 1 | null | null |
| 4.1 | 4 | 1 | null | null | null |
| 4.1.1 | 4 | 1 | 1 | null | null |
| 4.1.2 | 4 | 1 | 2 | null | null |