How Can MySQL Achieve Natural Sorting of Alphanumeric Strings?

MySQL’s elegant natural sorting

In MySQL, logically sorting strings containing numbers and letters can be a challenge. Consider the following example data set:

<code>最终幻想
最终幻想4
最终幻想10
最终幻想12
最终幻想12：普罗米西亚的锁链
最终幻想冒险
最终幻想起源
最终幻想战术</code>

How to achieve natural ordering so that Final Fantasy 10 appears after Final Fantasy 4 instead of before Final Fantasy 2?

Elegant solution

Instead of manually parsing the game name into its component parts (title, number, subtitle), try this straightforward solution:

<code class="language-sql">SELECT alphanumeric, 
       integer
FROM sorting_test
ORDER BY LENGTH(alphanumeric), alphanumeric;</code>

It works as follows:

1. LENGTH() function counts the number of characters in each alphanumeric field.
The
2. ORDER BY clause first sorts the rows in ascending length order. This effectively groups together lines with the same number of characters.
3. Within each group, rows are sorted in ascending order based on their alphanumeric value.

This method seamlessly handles game titles containing numbers and text, ensuring a natural ordering that meets your requirements. It avoids the hassle of manual parsing and is equally robust even when handling exceptions like "Warhammer 40,000" and "James Bond 007".

The above is the detailed content of How Can MySQL Achieve Natural Sorting of Alphanumeric Strings?. For more information, please follow other related articles on the PHP Chinese website!