-2

Input

REST_API_6007
REST_API_6
REST_API_4006
REST_API_4000
REST_API_3000
REST_API_3
REST_API_2
REST_API_1

Expected output :

REST_API_6007
REST_API_4006
REST_API_4000
REST_API_3000
REST_API_6
REST_API_3
REST_API_2
REST_API_1
philipxy
  • 14,867
  • 6
  • 39
  • 83
Kakarla Veera Shankara Ravindr
  • 15
  • 5
  • 1
    It's a good idea to be more specific. (e.g try to describe better what you want to do or add your code) – willy Sep 15 '22 at 07:09
  • 2
    Please do not add lots of different RDBMS. Only tag the one that you are using. – MT0 Sep 15 '22 at 07:11
  • Don't keep your own copy of a post, other people may have edited it, click on 'edit' to edit & don't overwrite if you have been told someone else has edited. You removed an edit improving format. (Please read the edit help.) – philipxy Sep 15 '22 at 07:59

1 Answers1

0

In Oracle, use SUBSTR to split the string and then ORDER BY the string part and then the numeric part:

SELECT *
FROM   table_name
ORDER BY SUBSTR(column_name, 1, 9) DESC,
         TO_NUMBER(SUBSTR(column_name, 10)) DESC

Or using INSTR to find the last underscore:

SELECT *
FROM   table_name
ORDER BY SUBSTR(column_name, 1, INSTR(column_name, '_', -1)) DESC,
         TO_NUMBER(SUBSTR(column_name, INSTR(column_name, '_', -1) + 1)) DESC;

Or regular expressions:

SELECT *
FROM   table_name
ORDER BY REGEXP_SUBSTR(column_name, '^(\D+)(\d+)$', 1, 1, NULL, 1) DESC,
         TO_NUMBER(REGEXP_SUBSTR(column_name, '^(\D+)(\d+)$', 1, 1, NULL, 2)) DESC;

fiddle

In other RDBMS, use the same techniques with the appropriate functions for finding sub-strings and converting strings to numbers.

MT0
  • 143,790
  • 11
  • 59
  • 117