MySQL Natural Sorting

Created with Sketch.

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
);

Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the items table:

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

Code language: SQL (Structured Query Language) (sql)

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

SELECT
item_no
FROM
items
ORDER BY item_no;

Code language: SQL (Structured Query Language) (sql)

MySQL Natural Sorting Example

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

MySQL Natural Sorting Example correct order

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 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;

Code language: SQL (Structured Query Language) (sql)

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

The disadvantage of this solution is that we have to break the 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 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;

Code language: SQL (Structured Query Language) (sql)

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');

Code language: SQL (Structured Query Language) (sql)

The expected result after sorting is as follows:

MySQL Natural Sorting Another Good Example

To achieve this result, we can use the LENGTH function. Notice that LENGTH 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;

Code language: SQL (Structured Query Language) (sql)

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 function 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.

Leave a Reply

Your email address will not be published.