Custom sorting in oracle

I am trying to sort a particular column “TENOR” on the basis on maturity (group by Currencies) for eg – it should be sorted in the following order ON, SW, 1M, 2M, 3M, 6M, 9M, 1Y, 18M, 2Y and so on.. where ON = Overnight, SW = Single week and 1M = 1 month. i have tried using order by case but not sure what I am missing it always gives 18M on the top of the result.

Answer

You can use a DECODE (or CASE) statement (and could put meaningful values) and, since you are getting a 3-character sub-string, you need to include the trailing space:

If a simplified version of your query is:

SELECT SUBSTR( name, 9, 3 ) AS tenor,
       name
FROM   table_name
ORDER BY SUBSTR( name, 1, 8 ),
         DECODE(
           tenor,
           'ON ', 1/30,
           'SW ', 7/30,
           '1M ', 1,
           '2M ', 2,
           '3M ', 3,
           '6M ', 6,
           '9M ', 9,
           '1Y ', 12,
           '18M', 18,
           '2Y ', 24,
           '3Y ', 36,
           '5Y ', 60,
           '10Y', 120,
           NULL
         ) ASC NULLS LAST

For the test data:

CREATE TABLE table_name ( name ) AS
SELECT 'ARZ USD 18M FX FORWARD' FROM DUAL UNION ALL
SELECT 'BRZ USD 10Y FX FORWARD' FROM DUAL UNION ALL
SELECT 'ARZ USD 1Y FX FORWARD' FROM DUAL UNION ALL
SELECT 'ARZ USD 2M FX FORWARD' FROM DUAL UNION ALL
SELECT 'BRZ USD 1M FX FORWARD' FROM DUAL UNION ALL
SELECT 'ARZ USD 6M FX FORWARD' FROM DUAL UNION ALL
SELECT 'ARZ USD 3M FX FORWARD' FROM DUAL UNION ALL
SELECT 'ARZ USD 1M FX FORWARD' FROM DUAL UNION ALL
SELECT 'ARZ USD 9M FX FORWARD' FROM DUAL UNION ALL
SELECT 'BRZ USD 1Y FX FORWARD' FROM DUAL;

This outputs:

TENOR | NAME                  
:---- | :---------------------
1M    | ARZ USD 1M FX FORWARD 
2M    | ARZ USD 2M FX FORWARD 
3M    | ARZ USD 3M FX FORWARD 
6M    | ARZ USD 6M FX FORWARD 
9M    | ARZ USD 9M FX FORWARD 
1Y    | ARZ USD 1Y FX FORWARD 
18M   | ARZ USD 18M FX FORWARD
1M    | BRZ USD 1M FX FORWARD 
1Y    | BRZ USD 1Y FX FORWARD 
10Y   | BRZ USD 10Y FX FORWARD

db<>fiddle here