CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Mukesh Kr. Singh
Mukesh Kr. Singh

Reputation: 11

How to sort alphanumeric String in oracle?

Input is:

Section1
Section2
Section3
Section10
Section11
Section1A
Section1B
Section12
Section11A
Section11B

And I want output like:

Section1
Section1A
Section1B
Section2
Section3
Section10
Section11
Section11A
Section11B
Section12

I tried query :

select section_name
from sections
order by length(section_name),section_name

Upvotes: 1

Views: 1026

Answers (1)

Aleksej
Aleksej

Reputation: 22949

Assuming that the structure of your strings is fixed, as in your example, this could be a way:

SQL> select x,
  2         to_number(regexp_substr(x, '[0-9]+')) numericPart,
  3         regexp_substr(x, '([0-9]+)([A-Z])', 1, 1, '', 2) optionalChar
  4  from (
  5          select 'Section1' x from dual union all
  6          select 'Section2' from dual union all
  7          select 'Section3' from dual union all
  8          select 'Section10' from dual union all
  9          select 'Section11' from dual union all
 10          select 'Section1A' from dual union all
 11          select 'Section1B' from dual union all
 12          select 'Section12' from dual union all
 13          select 'Section11A' from dual union all
 14          select 'Section11B' from dual
 15  )
 16  order by numericPart,
 17           optionalChar nulls first
 18  ;

X          NUMERICPART OPTIONALCHAR
---------- ----------- ----------------------------------------
Section1             1
Section1A            1 A
Section1B            1 B
Section2             2
Section3             3
Section10           10
Section11           11
Section11A          11 A
Section11B          11 B
Section12           12

Here you first order by the numeric part, treating it as number, and then consider the (optional) character after the number.

Upvotes: 1

Related Questions