MySQL Natural Sorting

MySQL Natural Sorting

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

UnsortedLexicographical Sorting (Default)Natural Sorting (Expected)
item1item1item1
item2item10item2
item10item2item10

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

idfilename
1file1.txt
2file10.txt
3file2.txt

Natural Sorting Query

SELECT * FROM files ORDER BY CAST(SUBSTRING_INDEX(filename, 'file', -1) AS UNSIGNED);

Output:

idfilename
1file1.txt
3file2.txt
2file10.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? 🚀

Soeng Souy

Soeng Souy

Website that learns and reads, PHP, Framework Laravel, How to and download Admin template sample source code free.

Post a Comment

CAN FEEDBACK
close