Generate a sort key for natural ordering. This function produces a key that allows strings containing numbers to be sorted in a human-readable order.
The NATURAL_SORT_KEY function is used for sorting that is closer to natural sorting. Strings are sorted in alphabetical order, while numbers are treated in a way such that, for example, 10 is greater than 2, whereas in other forms of sorting, 2 would be greater than 10, just like z is greater than ya.
There are multiple natural sort implementations, differing in the way they handle leading zeroes, fractions, i18n, negatives, decimals and so on.
MariaDB's implementation ignores leading zeroes when performing the sort.
You can use also use NATURAL_SORT_KEY with . The value is not stored permanently in the table. When using a generated column, the virtual column must be longer than the base column to cater for embedded numbers in the string and .
Unsorted, regular sort and natural sort:
Sorting IPs, unsorted, regular sort and natural sort::
Using with a :
Note that if the virtual column is not longer, results may not be as expected:
Ignoring leading zeroes can lead to undesirable results in certain contexts. For example:
This may not be what we were hoping for in a 'natural' sort. A workaround is to sort by both NATURAL_SORT_KEY and regular sort.
This page is licensed: CC BY-SA / Gnu FDL
NATURAL_SORT_KEY(str)CREATE TABLE t1 (c TEXT);
INSERT INTO t1 VALUES ('b1'),('a2'),('a11'),('a1');
SELECT c FROM t1;
+------+
| c |
+------+
| b1 |
| a2 |
| a11 |
| a1 |
+------+
SELECT c FROM t1 ORDER BY c;
+------+
| c |
+------+
| a1 |
| a11 |
| a2 |
| b1 |
+------+TRUNCATE t1;
INSERT INTO t1 VALUES
('5.5.31'),('10.7.0'),('10.2.1'),
('10.1.22'),('10.3.32'),('10.2.12');
SELECT c FROM t1;
+---------+
| c |
+---------+
| 5.5.31 |
| 10.7.0 |
| 10.2.1 |
| 10.1.22 |
| 10.3.32 |
| 10.2.12 |
+---------+
SELECT c FROM t1 ORDER BY c;
+---------+
| c |
+---------+
| 10.1.22 |
| 10.2.1 |
| 10.2.12 |
| 10.3.32 |
| 10.7.0 |
| 5.5.31 |
+---------+
SELECT c FROM t1 ORDER BY NATURAL_SORT_KEY(c);
+---------+
| c |
+---------+
| 5.5.31 |
| 10.1.22 |
| 10.2.1 |
| 10.2.12 |
| 10.3.32 |
| 10.7.0 |
+---------+TRUNCATE t1;
INSERT INTO t1 VALUES
('192.167.3.1'),('192.167.1.12'),('100.200.300.400'),
('100.50.60.70'),('100.8.9.9'),('127.0.0.1'),('0.0.0.0');
SELECT c FROM t1;
+-----------------+
| c |
+-----------------+
| 192.167.3.1 |
| 192.167.1.12 |
| 100.200.300.400 |
| 100.50.60.70 |
| 100.8.9.9 |
| 127.0.0.1 |
| 0.0.0.0 |
+-----------------+
SELECT c FROM t1 ORDER BY c;
+-----------------+
| c |
+-----------------+
| 0.0.0.0 |
| 100.200.300.400 |
| 100.50.60.70 |
| 100.8.9.9 |
| 127.0.0.1 |
| 192.167.1.12 |
| 192.167.3.1 |
+-----------------+
SELECT c FROM t1 ORDER BY NATURAL_SORT_KEY(c);
+-----------------+
| c |
+-----------------+
| 0.0.0.0 |
| 100.8.9.9 |
| 100.50.60.70 |
| 100.200.300.400 |
| 127.0.0.1 |
| 192.167.1.12 |
| 192.167.3.1 |
+-----------------+CREATE TABLE t(c VARCHAR(3), k VARCHAR(4) AS (NATURAL_SORT_KEY(c)) INVISIBLE);
INSERT INTO t(c) VALUES ('b1'),('a2'),('a11'),('a10');
SELECT * FROM t ORDER by k;
+------+
| c |
+------+
| a2 |
| a10 |
| a11 |
| b1 |
+------+CREATE TABLE t2(c VARCHAR(3), k VARCHAR(3) AS (NATURAL_SORT_KEY(c)) INVISIBLE);
INSERT INTO t2(c) VALUES ('b1'),('a2'),('a11'),('a10');
SELECT * FROM t2 ORDER by k;
+------+
| c |
+------+
| a2 |
| a11 |
| a10 |
| b1 |
+------+CREATE TABLE t3 (a VARCHAR(4));
INSERT INTO t3 VALUES
('a1'), ('a001'), ('a10'), ('a001'), ('a10'),
('a01'), ('a01'), ('a01b'), ('a01b'), ('a1');
SELECT a FROM t3 ORDER BY a;
+------+
| a |
+------+
| a001 |
| a001 |
| a01 |
| a01 |
| a01b |
| a01b |
| a1 |
| a1 |
| a10 |
| a10 |
+------+
10 rows in set (0.000 sec)
SELECT a FROM t3 ORDER BY NATURAL_SORT_KEY(a);
+------+
| a |
+------+
| a1 |
| a01 |
| a01 |
| a001 |
| a001 |
| a1 |
| a01b |
| a01b |
| a10 |
| a10 |
+------+SELECT a FROM t3 ORDER BY NATURAL_SORT_KEY(a), a;
+------+
| a |
+------+
| a001 |
| a001 |
| a01 |
| a01 |
| a1 |
| a1 |
| a01b |
| a01b |
| a10 |
| a10 |
+------+