MySQL Natural Sorting

Summary: in this tutorial, you will learn how to sort data using the natural sorting technique in MySQL.

What is natural sorting?

Natural sorting, also known as “human sorting” or “alphanumeric sorting,” is a way of arranging data that appears more intuitive to humans, particularly when dealing with data that includes a mix of numbers and text.

Natural sorting orders items based on their value as a human would expect, rather than treating them purely as strings.

Typically, natural sorting follows these principles:

• Sort Numerical Values First.
• Then sort Text Values Second.
• Ignore letter cases and leading zeros.
• Finally, sort symbols and special Characters using their ASCII or Unicode values.

Setting up a sample table

First, create a new table named items to store sample data:

CREATE TABLE items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);Code language: SQL (Structured Query Language) (sql)

The items table has two columns:

• id: This column uniquely identifies each row in the items table. It has the AUTO_INCREMENT attribute that automatically generates a sequential number when you insert a new row into the table.
• name: This column stores the name of each item.

Second, insert some rows into the items table:

INSERT INTO items(name)
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 values in the name column:

SELECT 
  name 
FROM 
  items 
ORDER BY 
  name;Code language: SQL (Structured Query Language) (sql)

Output:

+------+
| name |
+------+
| 1    |
| 10Z  |
| 1C   |
| 2    |
| 20D  |
| 2A   |
| 3C   |
+------+
7 rows in set (0.00 sec)Code language: JavaScript (javascript)

The result set may not be what we expected. We expect to have the sorted result set like this:

+------+
| name |
+------+
| 1    |
| 1C   |
| 2    |
| 2A   |
| 3C   |
| 10Z  |
| 20D  |
+------+
7 rows in set (0.00 sec)Code language: JavaScript (javascript)

Unfortunately, MySQL does not provide any built-in natural sorting syntax or function.

To perform a natural sorting, you can use various techniques. The following example shows how to use regular expressions to perform natural sorting.

MySQL natural sorting using regular expressions

First, split the name column into two parts using the REGEXP_SUBSTR() function:

SELECT
  name, 
  CAST(REGEXP_SUBSTR(name, '^\\d+') AS SIGNED) prefix, 
  REGEXP_SUBSTR(name, '\\D$') suffix 
FROM
  items;Code language: PHP (php)

Output:

+------+--------+--------+
| name | prefix | suffix |
+------+--------+--------+
| 1    |      1 | NULL   |
| 1C   |      1 | C      |
| 10Z  |     10 | Z      |
| 2A   |      2 | A      |
| 2    |      2 | NULL   |
| 3C   |      3 | C      |
| 20D  |     20 | D      |
+------+--------+--------+
7 rows in set (0.00 sec)Code language: PHP (php)

In this example, we use regular expressions to split the values in the name column into numerical and alphabetical parts:

• The pattern '^\\d+' matches one or more digits at the beginning of a string.
• The pattern '\\D$' matches one or more non-digit characters at the end of a string.

Since the REGEXP_SUBSTR() function returns a string, we use the CAST to convert it to an integer for the prefix column.

As a result, the prefix column stores the number part and suffix column stores the alphabetical part of the values in the name column.

Second, sort the values in the name column by the prefix and suffix columns:

SELECT 
  name, 
  CAST(
    REGEXP_SUBSTR(name, '^\\d+') AS SIGNED
  ) prefix, 
  REGEXP_SUBSTR(name, '\\D$') suffix 
FROM 
  items 
ORDER BY 
  prefix, 
  suffix;Code language: SQL (Structured Query Language) (sql)

Output:

+------+--------+--------+
| name | prefix | suffix |
+------+--------+--------+
| 1    |      1 | NULL   |
| 1C   |      1 | C      |
| 2    |      2 | NULL   |
| 2A   |      2 | A      |
| 3C   |      3 | C      |
| 10Z  |     10 | Z      |
| 20D  |     20 | D      |
+------+--------+--------+
7 rows in set (0.01 sec)Code language: PHP (php)

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

If you don’t want to have the prefix and suffix columns in the output, you can remove it as follows:

SELECT 
  name 
FROM 
  items 
ORDER BY 
  CAST(
    REGEXP_SUBSTR(name, '^\\d+') AS SIGNED
  ), 
  REGEXP_SUBSTR(name, '\\D$')Code language: SQL (Structured Query Language) (sql)

Output:

+------+
| name |
+------+
| 1    |
| 1C   |
| 2    |
| 2A   |
| 3C   |
| 10Z  |
| 20D  |
+------+
7 rows in set (0.00 sec)Code language: JavaScript (javascript)

Notice that some programming languages support natural sorting functions e.g., PHP provides the natsort() function that sorts an array using a natural sorting algorithm.

Summary

• Split data into parts and sort the parts to achieve natural sorting in MySQL.