sqloracle-databaseoracle-sqldeveloper

Oracle Order By Sorting: Column Values with character First Followed by number

I have column values as

AVG,ABC, AFG, 3/M, 150,RFG,567, 5HJ

Requirement is to sort as below:

ABC,AFG,AVG,RFG,3/M,5HJ,150,567

Any help?

Solution

  • If you want to sort letters before numbers, then you can test each character. Here is one method:

    order by (case when substr(col, 1, 1) between 'A' and 'Z' then 1 else 2 end),
         (case when substr(col, 2, 1) between 'A' and 'Z' then 1 else 2 end),
         (case when substr(col, 3, 1) between 'A' and 'Z' then 1 else 2 end),
         col