Sorting Alphanumeric data in Oracle

Question

I have an alphanumeric data like:

1
1a
1b
2
2b
10
10a

If I sort this data, output will be like:

1
1a
10
10a
2
2b

But I want output as:

1
1a
2
2b
10
10a

How to get this output with Oracle command?

Answer 1

So, as I understand, you want to sort by numeric part of your data. For this purpose you can use regular expression (to extract the numeric part) like this:

SQL> select str from
  2  (
  3  select '1' str from dual union all
  4  select '1a'  from dual union all
  5  select '1b'  from dual union all
  6  select '2' from dual union all
  7  select '2b'  from dual union all
  8  select '10'  from dual union all
  9  select '10a' from dual
 10  ) t
 11  order by to_number(regexp_substr(str, '^[[:digit:]]*')), str
 12  /

STR
---
1
1a
1b
2
2b
10
10a