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

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? 🚀