MySQL query column descending order when column has varchar+number

Kakarla Veera Shankara Ravindr 0
Viewed 34

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
mysql sql
1 Answers

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.

MT00
Related