select adr
from (
select '2' adr from dual union all
select '4' from dual union all
select '1a' from dual union all
select '3a' from dual union all
select '12' from dual union all
select '13' from dual union all
select '3b' from dual union all
select 'c' from dual union all
select '4a1' from dual)
order by
mod(nvl(to_number(replace(adr,ltrim(adr,'1234567890'))),0),2)
,nvl(to_number(replace(adr,ltrim(adr,'1234567890'))),0)
,nvl(ltrim(adr,'1234567890'),' ')
/
SQL> /
ADR
---
c
2
4
4a1
12
1a
3a
3b
13
9 rows selected.
|