CODEWITHSUNDEEP
sqlstringoracle-databasesql-order-byoracle12c
Rajesh A
Rajesh A

Reputation: 21

Oracle Alpha-Numeric column sorting

I am working on to Sort the Revision column of an Oracle DB table in the asc order as per below. At first the numeric revisions to be sorted (1,2,3,…). Thereafter Alpha-Numeric to be sorted as following: A, B, B1, C, C1, C2,…,Y, Y2, Y3, Z, AA, AB,..,DA, …ZZ, etc. Row_Number() in the SELECT statement to be filled with 1,2,3… for each document# (ABC, XYZ) after revision sorting out.

See the uploaded image for the required table.

I tried with SUBSTR , Order by, etc but failed to sort out as per above requirement. Can someone help me on this ? Thanks!

x

Upvotes: 1

Views: 299

Answers (2)

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

There is well known old method: rpad(col, max-length, '0')

For example rpad(col, max(length(col)) over(), '0'

Upvotes: 0

GMB
GMB

Reputation: 222432

As I understand your question, you want to put last revisions that contain only two characters and no digits.

You can use a conditional sort:

select 
    t.*, 
    row_number() over(
        partition by doc#
        order by 
            case when regexp_like(revision, '^\w\d?$') then 0 else 1 end,
            revision
    ) rn
from t
order by doc#, rn

The regular expression describes a string starting with an alphanumeric character, optionally followed by a digit: these revisions should come first. Demo on DB Fiddle:

with t as (
    select 'ABC' doc#, '1' revision from dual
    union all select 'ABC', '2' from dual
    union all select 'ABC', '3' from dual
    union all select 'ABC', 'A' from dual
    union all select 'ABC', 'B' from dual
    union all select 'ABC', 'B1' from dual
    union all select 'ABC', 'C' from dual
    union all select 'ABC', 'C1' from dual
    union all select 'ABC', 'D' from dual
    union all select 'ABC', 'AA' from dual
    union all select 'ABC', 'AB' from dual
    union all select 'ABC', 'BA' from dual
    union all select 'ABC', 'DA' from dual
)
select 
    t.*, 
    row_number() over(
        partition by doc#
        order by 
            case when regexp_like(revision, '^\w\d?$') then 0 else 1 end,
            revision
    ) rn
from t
order by doc#, rn

DOC# | REVISION | RN
:--- | :------- | -:
ABC  | 1        |  1
ABC  | 2        |  2
ABC  | 3        |  3
ABC  | A        |  4
ABC  | B        |  5
ABC  | B1       |  6
ABC  | C        |  7
ABC  | C1       |  8
ABC  | D        |  9
ABC  | AA       | 10
ABC  | AB       | 11
ABC  | BA       | 12
ABC  | DA       | 13

Upvotes: 1

Related Questions