Natural sorting with ORDER BY clause

The data in MySQL table can be selected and sorted using SELECT and ORDER BY statements. The ORDER BY works well with alphabets and number columns. But when the column is alpha-numeric, we may not get desired output, because, in MySQL the alphabets are sorted in linear way. Starting from first character, sorting happens at one character at a time. To get desired output user has to define a way of sorting the columns. This is called 'natural sorting'.

Consider a table having alpha-numeric data such as area code in vehicle registration or item code.

regCode

9DL

90HA

22KA

2MA

99PJ

1PY

These data, when sorted in an ordinarily manner, produce result like this:

SELECT regCode FROM tblreg ORDER BY regCode;

regCode

1. 1PY
2. 22KA
3. 2MA
4. 90HA
5. 99PJ
6. 9DL

But we expected the result to be:

regCode

1. 1PY
2. 2MA
3. 9DL
4. 22KA
5. 90HA
6. 99PJ

Solution to this problem is to split the alphanumeric column into two columns containing alphabets and numbers. Column 'alpha' has alphabets and 'num' has numbers. We can sort the data and based on these columns to get desired output.

SELECT CONCAT(alpha, num)

FROM tblregistration

ORDER BY alpha, num;

This way works good, except that it requires the data to be stored and manipulated in separate columns. To fix that, we can use CAST or LENGTH functions as given below:

SELECT * FROM `tblreg` ORDER BY CAST(`regCode` AS UNSIGNED), `regCode`;

The syntax first converts the data into unsigned integer using CAST. Then it is sorted numerically first and then alphabetically.

When the alphanumeric data is of different length, the LENGTH functions can be used for sorting, as shown below.

SELECT * FROM `tblreg` ORDER BY LENGTH(`regCode`), `regCode`;