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)
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 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:
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.