MySQL Natural Sorting

 MySQL Natural Sorting

Summary: in this tutorial, you will learn about some natural sorting techniques in MySQL using the ORDER BY clause.

Setting up a sample table

First, create a new table named items by using the following  CREATE TABLE statement:

CREATE TABLE items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_no VARCHAR(255) NOT NULL
);

Second, insert some rows into the items table:

INSERT INTO items(item_no)
VALUES ('1'),
       ('1C'),
       ('10Z'),
       ('2A'),
       ('2'),
       ('3C'),
       ('20D');

Third, query data from the items table sorted by the item_no:

SELECT 
    item_no
FROM
    items
ORDER BY item_no;

This may not what we expected. We expect to see the result like the following picture:

This is called natural sorting. Unfortunately, MySQL does not provide any built-in natural sorting syntax or function. The ORDER BY clause sorts strings in a linear fashion i.e., one character at a time, starting from the first character.

MySQL natural sorting examples

To work around this, first, we split the item_no column into 2 columns: prefix and suffix. The prefix column stores the number part of the item_no and suffix column stores the alphabetical part. Then, we can sort the data based on these columns as shown in the following query:

SELECT 
    CONCAT(prefix, suffix)
FROM
    items
ORDER BY 
    prefix , suffix;

The query first sorts of data numerically and then sorts the data alphabetically. We get the expected result.

The disadvantage of this solution is that we have to break them item_no into two parts before inserting or updating it. In addition, we have to combine two columns into one when we select the data.

If the item_no data is in a fairly standard format, you can use the following query to perform natural sorting without changing the table structure.

SELECT 
    item_no
FROM
    items
ORDER BY CAST(item_no AS UNSIGNED) , item_no;

In this query, first, we convert item_no data into an unsigned integer using the type cast. Second, we use the ORDER BY clause to sort the rows numerically first and alphabetically then.

Let’s take a look at another common set of data that we often have to deal with.

TRUNCATE TABLE items;

INSERT INTO items(item_no)
VALUES('A-1'),
      ('A-2'),
      ('A-3'),
      ('A-4'),
      ('A-5'),
      ('A-10'),
      ('A-11'),
      ('A-20'),
      ('A-30');

The expected result after sorting is as follows:

To achieve this result, we can use the LENGTH function. Notice that LENGTH the function returns the length of a string. The idea is to sort the item_no data by length first and then by column value as the following query:

SELECT 
    item_no
FROM
    items
ORDER BY LENGTH(item_no) , item_no;

As you see the data is sorted naturally.

In case, all the above solutions didn’t work for you. You need to perform natural sorting in the application layer. Some languages support natural sorting functions e.g., PHP provides the natsort() function that sorts an array using natural sorting algorithm.

In this tutorial, you have learned how to use some techniques to perform natural sorting in MySQL.