Oracle Sql | Modifying Data In Order By

following structure in a ORACLE table: FILE_NAME ----------- 12345_l.tif 12345_m.tif 12345_r.tif 12345_x.tif 12345_y.tif Need the following result: First *_m* Then *_l* Then *_r*

Solution 1:

Change your ORDER BY to order it by a numeric:

ORDER BY regexp_replace(FILE_NAME, '_m', 1),
          regexp_replace(FILE_NAME, '_l', 2),
          regexp_replace(FILE_NAME, '_r', 3);

e.g.

WITH t
  AS (SELECT'12345_l.tif'AS file_name FROM dual
      UNIONSELECT'12345_m.tif'FROM dual
      UNIONSELECT'12345_r.tif'FROM dual
      UNIONSELECT'12345_x.tif'FROM dual
      UNIONSELECT'12345_y.tif'FROM dual)
SELECT file_name
  FROM t
 ORDERBY regexp_replace(FILE_NAME, '_m', 1),
          regexp_replace(FILE_NAME, '_l', 2),
          regexp_replace(FILE_NAME, '_r', 3);

Gives:

==============
12345_m.tif
12345_l.tif
12345_r.tif
12345_x.tif
12345_y.tif

Hope it helps...

Alternatively you could use:

ORDERBY (CASE SUBSTR(file_name, INSTR(file_name, '_')+1, 1)
             WHEN'm'THEN1WHEN'l'THEN2WHEN'r'THEN3ELSE4END) ASC;

E.G.:

WITH t
  AS (SELECT'12345_l.tif'AS file_name FROM dual
      UNIONSELECT'12345_y.tif'FROM dual
      UNIONSELECT'12345_r.tif'FROM dual
      UNIONSELECT'12345_x.tif'FROM dual
      UNIONSELECT'12345_m.tif'FROM dual)
SELECT file_name
  FROM t
 ORDERBY (CASE SUBSTR(file_name, INSTR(file_name, '_')+1, 1)
             WHEN'm'THEN1WHEN'l'THEN2WHEN'r'THEN3ELSE4END) ASC;

Gives:

12345_m.tif
12345_l.tif
12345_r.tif
12345_x.tif
12345_y.tif