How to natural sort “X-Y” string data, first by X and then by Y?

user_1007974 0
Viewed 64

Given this data:

W18-40461
W19-1040
W20-4617
W20-100

I've tried several of the common natural sorting methods for mysql, but they won't sort these in a natural descending way, like:

W20-4617
W20-100
W19-1040
W18-40461

For example:

select theID 
from Table 
where theID 
order by lpad(theID, 9, 0) desc
natural-sort mysql sql
2 Answers

Assuming the parts on either side of the - are limited to 2 digits and 5 digits respectively, you can extract the two numeric values using SUBSTR (and LOCATE to find the - between the two numbers) and then LPAD to pad each of those values out to 2 and 5 digits to allow them to be sorted numerically:

SELECT *
FROM data
ORDER BY LPAD(SUBSTR(id, 2, LOCATE('-', id) - 2), 2, '0') DESC,
         LPAD(SUBSTR(id, LOCATE('-', id) + 1), 5, '0') DESC

Output (for my expanded sample):

id
W20-12457
W20-4617
W20-100
W19-1040
W18-40461
W4-2017

Demo on db-fiddle

If the values can have more than 2 or 5 digits respectively, just change the second parameters to LPAD to suit.

user_94737640

I would do this as:

order by substring_index(col, '-', 1) desc,
         substring_index(col, '-', -1) + 0 desc

This orders by the part before the hyphen as a string. And it converts the part after the hyphen to a number for sorting purposes.

user_11440350
Related