MySQL Natural Sorting
What is Natural Sorting?
Natural sorting is a way of sorting alphanumeric values in the way humans expect rather than lexicographically.
Example of Lexicographical vs. Natural Sorting
Unsorted | Lexicographical Sorting (Default) | Natural Sorting (Expected) |
---|---|---|
item1 | item1 | item1 |
item2 | item10 | item2 |
item10 | item2 | item10 |
Issue with Default Sorting (ORDER BY
)
SELECT * FROM products ORDER BY name;
- This will sort values character by character rather than considering numeric values properly.
How to Perform Natural Sorting in MySQL?
1. Use CAST()
or CONVERT()
to Convert Numbers
If numbers are stored as part of a string, extract and cast them to integers before sorting.
SELECT * FROM products ORDER BY CAST(SUBSTRING_INDEX(name, 'item', -1) AS UNSIGNED);
2. Using LENGTH()
for Proper Ordering
To handle cases where numbers have leading zeros:
SELECT * FROM products ORDER BY LENGTH(name), name;
3. Using LOCATE()
for Mixed String-Number Sorting
If the numeric part isn’t at the end, locate it first:
SELECT * FROM products ORDER BY
CAST(SUBSTRING(name, LOCATE('item', name) + 4) AS UNSIGNED);
Example: Natural Sorting on a Table
Table: files
id | filename |
---|---|
1 | file1.txt |
2 | file10.txt |
3 | file2.txt |
Natural Sorting Query
SELECT * FROM files ORDER BY
CAST(SUBSTRING_INDEX(filename, 'file', -1) AS UNSIGNED);
Output:
id | filename |
---|---|
1 | file1.txt |
3 | file2.txt |
2 | file10.txt |
Summary
✔ Lexicographical sorting does not work well for mixed strings & numbers.
✔ Use CAST()
and SUBSTRING_INDEX()
to extract numeric parts and sort naturally.
✔ Consider LENGTH()
and LOCATE()
for complex cases.
Would you like help optimizing sorting for your specific dataset? 🚀