This query:
with T1 as (
select 'A4' from dual
union
select 'A3' from dual
union
select 'A100' from dual
union
select 'A20' from dual )
select * from t1;
produces the normal sort:
A100
A20
A3
A4
In recent Oracle versions ( 19 and later ) is there any option that allows to get this result:
A3
A4
A20
A100
?
Of course it is possible to write a function to take care of this peculiar sorting but I am wondering if this can be produced just by an option ( it is possible in other databases ).
No - you'll have to manually extract the numbers out. There's no built-in option to do this.
Sidenote - there's no ORDER BY on the query. So it's only sorted because it happens to use SORT-UNIQUE to apply the deduplication required by UNION. It's possible it could use other methods on other versions.
With UNION ALL (which avoids the de-duplication step), it returns the rows in the same order they're listed in the WITH clause:
with T1 as (
select 'A4' from dual
union all
select 'A3' from dual
union all
select 'A100' from dual
union all
select 'A20' from dual )
select * from t1;
'A4'
----
A4
A3
A100
A20