Sort Alphanumeric column in Oracle SQL

Question

I am not an SQL expert. I've been searching over google on how to achieve below. I need to sort my records based on ID. (prefix first and then their numerical values)

Table: CUSTOMER_TRANS

| ID       | Name   | Date       |
|==========|========|============|
|CP-091435 | Ola    | 01-01-2010 |
|WM-183258 | Tor    | 09-09-2001 |
|CP-109056 | Jess   | 03-03-2003 |


SELECT * FROM CUSTOMER_TRANS ORDER BY substr(ID, 4) desc;

I need to sort first the 2 prefix e.g ES and then the numerical values. However, my SQL above returns only the numerical highest WM-183258. The expected result is to returns first the "CP" prefix and the highest in numerical value e.g. below. Hope someone can give me somelight.

Expected Result:

| ID       | Name   |
|==========|========|
|CP-109056 | Ola    | 
|CP-091435 | Jess   | 
|WM-183258 | Tor    | 

Answer 1

My PL/SQL is quite rusty but you should be able to use something like

... ORDER BY substr(ID, 1, 2) ASC, substr(ID, 4) DESC

or even better as pointed out by mathguy

... ORDER BY substr(ID, 1, 2) ASC, ID DESC

That is, sort by the first two characters ascending, then by the rest descending.

---

That's probably quite sub-optimal from a performance perspective. I would consider breaking up that ID into it's parts, eg

ID_PREFIX CHAR(2),
ID_SUFFIX CHAR(6) -- or a numeric type, whatever is appropriate

and create your primary key on both. That makes it easy to group and sort and for display, you can just use

SELECT ID_PREFIX || '-' || ID_SUFFIX AS ID...