Oracle: How to sort mixed alphanumeric in a logical numeric order (while avoiding the ORA-01722 erro

[sloot]

I added a new select at the bottom that deals with decimal numbers

create table sort_test
(string varchar2(10));
insert into sort_test values (1);
insert into sort_test values ('a');
insert into sort_test values (11);
insert into sort_test values ('123B');
insert into sort_test values ('A2');
insert into sort_test values ('2A');
insert into sort_test values ('b');
insert into sort_test values ('A');
insert into sort_test values (2);
commit;
select * from sort_test
order by regexp_replace( string
, '[[:digit:]]+'
, lpad(regexp_substr(string,'[[:digit:]]+'),10,'0')
)
;
STRING
1
2
2A
11
123B
A
A2
a
B

select *
from sort_test
order by regexp_replace( string, '[[:digit:].]+',
ltrim( to_char(regexp_substr(string,'[[:digit:].]+'),'0000000009.90000') )), string
;
STRING
0
0.12A
1
2
2A
2.23A
11
123B
A
A0
A.123
A0.123
A2
A2.0
A2.34
a
b

Note: this only works up to 10 digits, but that can be changed by
increasing the 10 parameter in the regexp_substr